?
Solved

VBA to delete rows in filtered table

Posted on 2012-03-13
7
Medium Priority
?
2,153 Views
Last Modified: 2012-03-13
Hi Experts!

Attached is some code I'm using to Filter one column in a Table and then delete the visible rows.  I had difficulty with Selection.EntireRow.Delete but have replaced it with RngToDelete.Delete.

My problem is that the code stops and asks if I want to "Delete Entire Sheet Row?".  I click "Yes" and it's fine.  How can I have the code eliminate that step as well so it runs from start to finish without stopping?

This is very likely a no brainer for all of you. My attempt at code is below.  Thank you!

Sub Filter_Sales1()
'
    Dim RngToDelete As Range
    Range("TblFTE").Select
   
    ActiveSheet.ListObjects("TblFTE").Range.AutoFilter Field:=10, Criteria1:= _
        Array("Business Development", "G&A", "Marketing", "R&D", "Solutions", "Support"), _
        Operator:=xlFilterValues
       
    Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
     
    RngToDelete.Delete
       
    Range("TblFTE[[#Headers],[EE ID]]").Select
    ActiveSheet.ShowAllData
End Sub
0
Comment
Question by:xllvr
[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
  • 4
  • 3
7 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37717688
try this (see line 12 modification):

Sub Filter_Sales1()
'
    Dim RngToDelete As Range
    Range("TblFTE").Select
    
    ActiveSheet.ListObjects("TblFTE").Range.AutoFilter Field:=10, Criteria1:= _
        Array("Business Development", "G&A", "Marketing", "R&D", "Solutions", "Support"), _
        Operator:=xlFilterValues
        
    Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
     
    RngToDelete.EntireRow.Delete
        
    Range("TblFTE[[#Headers],[EE ID]]").Select
    ActiveSheet.ShowAllData
End Sub

Open in new window


Also, when you want to avoid Excel prompting and you don't know exactly how to do the statement so you don't get the prompt (and some don't have - e.g., for deleting sheets, you would get prompted), then use:

Application.DisplayAlerts = False

before the command that would cause the prompt

Then,

Application.DisplayAlerts = True 'continue as normal
0
 
LVL 1

Author Comment

by:xllvr
ID: 37717714
Hey dlmille!  Nice to see you here.

Thanks for posting.  The code hung up on line 11 when I changed line 12.  Thoughts?
0
 
LVL 1

Author Comment

by:xllvr
ID: 37717733
Hang on a second.  Just tried your suggestion with Application.DisplayAlerts=False along with the code I already had and it seems to work.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 37717744
I'm not sure what you mean by "hung up" and I don't see a line 11.

Yes, displayalerts would also work, but it shouldn't be needed if you delete using the entirerow property.

Dave
0
 
LVL 1

Author Comment

by:xllvr
ID: 37717765
Apologies...Line 10.  The code halted at that point.  I originally used EntireRow and had problems with it.  I have read quite a few posts that showed the very same problem with using EntireRow in the code so I replaced it with what others used successfully.  Using RngToDelete.Delete worked fine except I didn't want to have to stop and click "Yes" to Delete Entire Sheet Row to continue the code.  

Using RngToDelete.Delete with the Display Alert message seems to be working.  Thanks for sending that along as a potential solution!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37717779
Perhaps it was choking on EntireRow because it was a table?  Yes, any time there is an Excel Prompt you want to circumvent (almost anytime, there are a few exceptions), you can override and take the default "YES" with Application.DisplayAlerts = False.

Dave
0
 
LVL 1

Author Comment

by:xllvr
ID: 37717791
That's the only conclusion I could come up with as well.  Grateful for the prompt tip, Dave!

-Nancy
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!

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

764 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