Delete Table Rows
Results 1 to 2 of 2

Thread: Delete Table Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,399
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete Table Rows

    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

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,903
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Delete Table Rows

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •