Solved

AutoFilter Delete

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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