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

Question

I have two columns, Column (A) and Column (B) in a spreadsheet.

Column (A) contains names extracted from a query (ex. Brian, Bob, Bill, etc...) and column (B) contains one of three statuses (Assigned, In Progress, or Pending).

However, this query sometimes pulls up some line items showing "Assigned" for the status with no name, therefore corresponding cell representing the name in Column (A) is blank. So I manually fill in those empty cells with "Unknown".

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

So the conditions are:

1. Blank cell in column (A)

2. Correspoding cell to its right (column B) contains the word "assinged"

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   

Answer 1

There is no need to loop here, take advantage of excels built in methods which 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