[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

AutoFilter Delete

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
RWayneH
Asked:
RWayneH
1 Solution
 
Harry LeeCommented:
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
 
RWayneHAuthor Commented:
Thanks it worked great!! -R-
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now