Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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