Solved

VBA Excel 2007 deleting visible rows in a table

Posted on 2011-09-04
3
335 Views
Last Modified: 2012-06-27
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
Comment
Question by:Thrawn3000
  • 2
3 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 36482525
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
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 36482528
NB: Replace Selection with:
wksRawDataLastWeek.Range("tblRawDataLastWeek")
0
 

Author Closing Comment

by:Thrawn3000
ID: 37003914
Hi
This has done exactly what I needed it to do, sorry for the late reply wwas on leave.

cheers
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question