Solved

AutoFilter Delete

Posted on 2014-01-06
2
261 Views
Last Modified: 2014-01-08
I have a dataset that already has some columns filtered (another procedures exit point)  and now need another column filtered and if there is a result (usually is) delete all rows that are greater than zero.

The column is AC, so I need to add to the existing filter, the deleting of all rows greater than zero.  There has been times when there has not been any zeros to filter by, but there has always so far rows greater than zero.

Is this possible, if a filter already in place?  Please advise and thanks. -R-
0
Comment
Question by:RWayneH
2 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39759860
RWayneH,

Are you trying to do this manually or are you trying to do this by VBA?

Either way, you can achieve this easily by first applying all the filters you want, including that Greater than Zero filter.

Then select your whole data range (not including your header rows). Then use F5 (goto) then select Special. In the Special popup window, choose Visible Cells Only, then click ok.

Right click on one of the selected row number and delete rows.

In VBA,

you can do something like this.

    With ActiveSheet
        .AutoFilterMode = False
        With Range("A1:A100000")
            .AutoFilter 1, "="
            On Error Resume Next
            Range("A2:A100000").SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With

Open in new window


In this code example, it will filter range A1 to A100000 for anything equal to blanks. Then select all visible cells within A1 to A100000 and delete the rows.

If you can upload a sample file, I can have the VBA code altered for you.
0
 

Author Closing Comment

by:RWayneH
ID: 39765681
Thanks it worked great!! -R-
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now