Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

VBA Excel 2007 deleting visible rows in a table

Hi Experts
I'm trying to filter a table and then delete only the visible rows in an excel table  a snippet of my code below prompts the error

"Delete method of range classs failed"

sheet name: wksRawDataLastWeek
table name: tblRawDataLastWeek


can you assist?


wksRawDataLastWeek.Range("tblRawDataLastWeek").SpecialCells(xlCellTypeVisible).EntireRow.Delete


cheers
0
Thrawn3000
Asked:
Thrawn3000
  • 2
1 Solution
 
jkpieterseCommented:
You cannot delete non-contiguous rows in one go. I suggest two options:
1. Sort the table prior to filtering so all rows that need to be removed are together
2. Loop through the visible rows from the bottom up to delete them.
Code for # 2:
Sub DelVisible()
    Dim lCt As Long
    For lCt = Selection.SpecialCells(xlCellTypeVisible).Areas.Count To 1 Step -1 'wksRawDataLastWeek.Range("tblRawDataLastWeek").SpecialCells(xlCellTypeVisible).areas.count
        If Selection.SpecialCells(xlCellTypeVisible).Areas(lCt).Cells(1, 1).Row <> 1 Then
            Selection.SpecialCells(xlCellTypeVisible).Areas(lCt).EntireRow.Delete
        Else
            'Do not delete row 1!
            With Selection.SpecialCells(xlCellTypeVisible).Areas(lCt)
                .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
            End With
        End If
    Next
End Sub

Open in new window

0
 
jkpieterseCommented:
NB: Replace Selection with:
wksRawDataLastWeek.Range("tblRawDataLastWeek")
0
 
Thrawn3000Author Commented:
Hi
This has done exactly what I needed it to do, sorry for the late reply wwas on leave.

cheers
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now