Deleting an entire row in VBA Macro

[tanyaleblanc]

I've created a filter for an account #, if that account # appears in my data file, I want to delete the entire row. This has worked before, but now I'm getting a debug error

 lRow = ActiveSheet.Range("A500").End(xlUp).RowIf AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A500").End(xlUp).Row
    
    With ActiveSheet
        .AutoFilterMode = False
        With Range("a8:W" & lRow)
            .AutoFilter
            .AutoFilter Field:=6, Criteria1:="469777"
           
        End With
    End With

 If Range("A8:A" & lRow).SpecialCells(xlCellTypeVisible).Count > 1 Then[/B][B]        ActiveSheet.Range("a8:W" & lRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete[/B]
[B]    End If

Error - Run-time error '1004' - no cells were found

 

[Fluff]

Try

   With ActiveSheet
      If .AutoFilterMode = True And .FilterMode = True Then .ShowAllData
      lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
      .Range("a8:W" & lRow).AutoFilter Field:=6, Criteria1:="469777"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .ShowAllData
   End With

This replaces all the code that you have shown

 

[tanyaleblanc]

It's perfect, thank you

 

[Fluff]

You're welcome & thanks for the feedback