Solved

AutoFilter Delete

Posted on 2014-01-06
2
281 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

822 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