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

x
?
Solved

Deleting rows of data that are results of Autofilter?

Posted on 2014-02-06
4
Medium Priority
?
207 Views
Last Modified: 2014-02-08
I am looking for a VBA script that is simple and deletes all visible rows in an autofilter.  I already have the filters applied, I just need to delete everything (except the headers).  I find myself needing this more often..  or better yet, have a visible property that would delete visible if true, or delete all hidden if false.. sort of a tool to use when dealing with autofilters.  Hope this makes sense. -R-
0
Comment
Question by:RWayneH
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Steve
ID: 39840348
ActiveSheet.Range("$A$1:$Z$65535).SpecialCells _
    (xlCellTypeVisible).EntireRow.Delete

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39840708
so you are assuming I only have 65K rows?  Does this allow for header row?  need to keep that
0
 
LVL 7

Accepted Solution

by:
Steve earned 2000 total points
ID: 39840858
ActiveSheet.Range("$A$1:$Z$65535").Offset(1, 0).SpecialCells _
    (xlCellTypeVisible).EntireRow.Delete

Open in new window

This will work with your header rows. And you can either adjust the column and row in the second half or do a count for them and use a variable.

I had left the second quote off the first example....
0
 

Author Closing Comment

by:RWayneH
ID: 39844073
Thanks. -R-
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

971 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