Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Excel 2007 deleting visible rows in a table

Posted on 2011-09-04
3
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

670 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