Geek asks and answers

Excel VBA macro: finding the first empty cell in a column and automatically filling it

I have two columns: column (A) and column (B) in a spreadsheet.

Column (A) contains the names extracted from the query (for example, Brian, Bob, Bill, etc.), and column (B) contains one of three statuses (Assignment, Execution, or Pending).

However, this query sometimes pulls out some items indicating "Assigned" for a status with no name, so the corresponding cell representing the name in column (A) is empty. Therefore, I manually fill in these empty Unknown cells.

What I want to do is create a macro that finds every empty cell in column (A) and fill in the word "Unknown" if the cell on the right is in the word "Assinged".

Thus, the conditions:

  • Empty field in column (A)

  • The corresponding cell on the right (column B) contains the word "asseded"

This is my code:

Private Sub CommandButton2_Click()

    For Each cell In Columns("A")
        If ActiveCell.Value = Empty And ActiveCell.Offset(0, 1).Value = "Assigned" Then ActiveCell.Value = "Unknown"
    Next cell

End Sub
+5
source share
3 answers

Welcome to SO.

Try this code. It will work a little faster and should get what you want.

Update : made the code more bulletproof!

Private Sub CommandButton2_Click()

Dim cel As Range, rngFind As Range, rngFilter As Range
Dim wks As Worksheet

Set wks = Sheets("sheet1")

With wks

    '-> Error check to make sure "blanks" exist
    Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole)

    If Not rngFind Is Nothing Then

        Set rngFilter = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)

        rngFilter.AutoFilter 1, "="

        '-> Error check to make sure "assigned" exists for blank cells
        Set rngFind = .Columns("B:B").SpecialCells(xlCellTypeVisible).Find("Assigned", lookat:=xlWhole)

        If Not rngFind Is Nothing Then
        '-> okay, it exists. filter and loop through cells

            rngFilter.AutoFilter 2, "Assigned"

            Set rngFind = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(1)).SpecialCells(xlCellTypeVisible)

            For Each cel In rngFind

                If cel.Offset(0, 1).Value = "Assigned" Then cel.Value = "Unknown"

            Next cel

        End If

    End If

End With


End Sub
+2
source

There is no need to quote; take advantage of excels built into methods that will execute faster.

Private Sub CommandButton2_Click()

    Application.ScreenUpdating = False

    With ActiveSheet.UsedRange
        .AutoFilter Field:=1, Criteria1:=""
        .AutoFilter Field:=2, Criteria1:="Assigned"

        If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
            If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Columns(1).SpecialCells(xlCellTypeBlanks).Value = "Unknown"
            End If
        End If

        .AutoFilter
    End With

    Application.ScreenUpdating = True

End Sub
+8
source

,

  • "()"
  • B "asseded"
  • B
  • alt + :,
  • F2
  • type "unknown"
  • click ctrl+enter

Your bad data should be good now!

Obviously this is a non-vba-based solution, but if you can avoid coding it, perhaps in the best way.

+1
source

More articles:


All Articles