Deleting an entire row in VBA Macro

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
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

Code:
 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
Code:
 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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
Try
Code:
   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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,439
Messages
5,486,900
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top