Delete Table Rows

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,421
I am wanting to delete the visible rows in a table where a filter has been applied.

This is what I'm using at the moment
Code:
   With Sheets("Events").ListObjects("tbl_EventItems")
      .Range.AutoFilter Field:=1, Criteria1:=strEvent
   
      .DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

      .Range.AutoFilter Field:=1
   End With
The problem is that this deletes the entire row and not just the table rows.

As I have a pivot table to the right of the table I need to delete just the table rows and not the entire rows.


TIA
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
I don't think you can delete a row in a filtered table, so you need 2 steps, like this:
I assumed there are no blank cell in column 1 before the ClearContents part, because the last line search the blank cells in column 1 then delete the rows.

Code:
   With ActiveSheet.ListObjects("Table1")
      .Range.AutoFilter Field:=1, Criteria1:="*e*"
   
      .DataBodyRange.SpecialCells(xlCellTypeVisible).ClearContents

      .Range.AutoFilter Field:=1
      
      .DataBodyRange.Columns(1).SpecialCells(xlCellTypeBlanks).Rows.Delete
   End With
 

Watch MrExcel Video

Forum statistics

Threads
1,102,447
Messages
5,486,954
Members
407,574
Latest member
Greso

This Week's Hot Topics

Top