Microsoft Excel - Autifilter only working for 2 criteria, delete visible rows not header

Asked By Rowland Hamilton on 05-Aug-11 11:25 PM

Earn up to 20 extra points for answering this tough question.

Folks:

Can you help? Array filtering works but no data gets deleted. If you know how to fix this way let me know.

I went with a simpler method and it worked for me, but I like how the array method knew how to determine the proper filter range, and make a variable out of the last row (something I was asking about for another macro).

I'll post my latest easier method code,but here are the details of my last problem:

LAST PROBLEMS DETAILS:

http://www.rondebruin.nl/delete.htm
Solution from above link won't work in my file but works in test file from link. When I modified the test file, adding spaces, replacing the terms with my terms in the Sheet and then modifying the code. (ALso, filtering doesn't seem to be case sensitive when I step through):

Code: myArr = Array("*Cost*", "*cost*", "*Total*", "*total*", "")

and

Code: .Range("C3:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

In the test file no problem, but my file, I step through it and watch the filters working but no rows are deleted. I saved the test file as an Excel 2010 xlsm file same as mine and it still worked.

Now, another forum user offered this delete code:

Sub FilteredRowsDeleteCode()
    Range("a4", Selection.SpecialCells(xlCellTypeLastCell)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete
End Sub

I stepped thru the other array code and stopped it when it got one of the array terms filtered, then ran the Sub FilteredRowsDeleteCode() and it worked, but I can't seem to join it with the other code, and get it inside the Active Sheet With statement, maybe because that statement defines ranges already so there is a conflict. I tried calling the separate macro but still no go.

Here is the Full modified test file code:

Sub Delete_with_Autofilter_Arrayx()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long

    With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

'Fill in the values that you want to delete
myArr = Array("*Cost*", "*cost*", "*Total*", "*total*", "")

For I = LBound(myArr) To UBound(myArr)

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("C3:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

    Set rng = Nothing
    With .AutoFilter.Range
    On Error Resume Next
    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
    .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rng Is Nothing Then rng.EntireRow.Delete
    End With

    'Remove the AutoFilter
    .AutoFilterMode = False
    End With

Next I

    With Application
    .ScreenUpdating = True
    .Calculation = calcmode
    End With

End Sub

Thanks, Rowland

ORIGINAL POSTING OF MINE FOR THIS QUESTION:

I abandoned this because you can't filter for more than 2 criteria but could just run this twice, particularly if filter criteria are not case sensitive:

PART I: How come I can't get my autofilter code to work for more than 2 criteria? I keep getting "Criteria doesn't exist" but why should that matter if I'm using "Or"?

If you have solution to only PART I or PART II, please respond.

PART II : Do you have a better idea for deleting the visible rows of filtered data below header row 3? How else can I select that range (something to do with choosing fields first through last?)?

CODE BEGINS:
Sub Remove_Totals_Blanks_Duplicate_Headers()
    'Remove Totals, Blanks and Duplicate Headers

'PART I
    Sheets("Incoming").Select
    ActiveSheet.AutoFilterMode = False

    Rows("3:3").Select
    Selection.AutoFilter

'Why doesn't filter work past the first 2 criteria?
    Range("A3:I3").AutoFilter Field:=3, _
    Criteria1:="*Cost*", _
    Operator:=xlOr, Criteria2:="*cost*", _
    Operator:=xlOr, Criteria3:="*Total*", _
    Operator:=xlOr, Criteria4:="*total*", Operator:=xlOr, Criteria5:="="

'PART II
'Now how do I delete the filtered, visible, contiguous Field 3 rows below my header row 3?

    'I think this would do it (worked in my recorderd version)
   'Range("C3").Select
   'Selection.End(xlDown).Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.EntireRow.Delete

End Sub
CODE ENDS

Thank you, Rowland

Ravi S replied to Rowland Hamilton on 05-Aug-11 11:49 PM

HI

AutoFilter is an easy way to turn the values in Excel column into specific filters based on the cell contents. As example, by adding AutoFilter to the list above, I could filter the "Winery" column to only display items from Beauregard. All the other wineries remain on the Excel list, but don’t display.

To turn on autofilter,

1. Click any cell within your range.

2. From the Data menu, select Filter.

3. From the submenu, select AutoFilter.

Once you’ve enabled this feature, your columns display with a drop-down arrow to the right. If you click the arrow control, you’ll see all the values for that particular column. To turn off the feature, you deselect Autofilter from the same submenu.

In the example above, I can see all the entries that show in the YEAR column. Various years are omitted such as 1998 since that value isn’t represented on the spreadsheet.

What’s appealing about this filter feature is that the displayed list is dynamic. If I shift to the 1.5L column, you’ll see I have two more entries: (Blanks) and (NonBlanks). Excel includes these values since this column includes blank cells. You did not see that choice for “YEAR” since there were no blank cells. And since I haven’t added anything to this column, no other values show.

What’s equally useful is these filters also adjust based on other autofilters. For example, if I filter the “Winery” column for “Ridge Vineyards” as shown below, my autofilter list for “YEAR” only displays Ridge values. Although the shipment contained items from the year 2000, none were from Ridge.

Excel Custom AutoFilters

As handy as these filters are, there are times when you need tofilter based on specific criteria within a cell. Going back to our first task of sorting bottles sizes, we can use a custom filter to find these items. This works because the distributor puts 1.5L in the “Description” line.

To set a simple custom autofilter,

1. Enable autofilter for your spreadsheet using the steps in the section above.

2. Click the down control arrow in the column you wish to filter.

3. From the drop down list select (Custom…) The Custom AutoFilter dialog opens. Your column name should show above the first list box.

4. In the first list box, click the drop down arrow and select your criteria.

5. In the list box to the right, either type a value or select one from the list.

6. Click OK.

In the example below, I’ve elected to filter for rows containing “1.5L” anywhere in the description. Once my filtered records appear, I’ll add an “X” in 1.5L column.

Excel is versatile in the filter settings. You’re not limited to just items containing a specific value. You could also use:

equals
does not equal
is greater than
is greater than or equal to
is less than
is less than or equal to
begins with
does not begin with
ends with
does not end with
contains
does not contain

Sometimes you need a more complicated filter. For example, I may want to select a row if two conditions are met. I might also be interested in selecting a cell if one or another condition is met.

refer link

http://www.timeatlas.com/5_minute_tips/general/excel_autofilter_makes_spreadsheets_more_useful

http://www.ozgrid.com/Excel/AutoFilters.htm

Ravi S replied to Rowland Hamilton on 05-Aug-11 11:52 PM

HI

Filter One Column

With the Excel Table, or Excel 2007 AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns.

For example, to filter for a specific customer,

follow these steps:

Click the drop down arrow at the right of the Customer heading
In the list of Customers, click Select All, to remove all the check marks
Click the check box for the customer that you want to filter for, such as MegaStore.
Click OK, to see the filtered list.

In the filtered worksheet, you'll see some changes:

only the rows for the selected customer are visible -- the other customer rows have been hidden

the row numbers for the filtered rows are shown in blue font.

the drop down arrow in the Customer column has changed to a filter symbol, with a small arrow

Filter Two Columns

After you have filtered one column, you can refine the filtered results, by filtering two or more columns.

For example, after you filter for a specific customer,

follow these steps to add a product filter:

Click the drop down arrow at the right of the Product heading.
Note: When another column is filtered, you might not see the full list of products -- you'll only see the products for the filtered records.
In the list of Products, click Select All, to remove all the check marks
Click the check box, or multiple check boxes, for the product(s) that you want to filter for, such as Bananas and Grapes.
Click OK, to see the filtered list.

Clear an Excel 2007 AutoFilter

After you have applied one or more filters, you can clear a specific filter, or all the filters in the Excel Table.

Clear a Specific Filter

To clear a specific filter, and leave other filters turned on:

Click the drop down arrow in the column heading where you want to clear the filter
Click on Clear Filter From [column name]

Clear All Filters

Instead of clearing a specific filter, you can clear all the filters from the Excel Table, in one step:

Select a cell in the table.
On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Clear.
Note: This will also clear the Sort options that you have applied.

Filter for Blank Cells

If there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list.

To show only the rows with blank cells in that column, remove the check marks from the other items in the list.

Filter for Highest and Lowest Values

If a column contains numbers, you can filter to find the highest or lowest numbers that column. In this example, you'll filter for the 5 lowest values in the Total column.

Click the drop down arrow in the column heading, click Number Filters, and then click Top 10.
Note: If fewer than half of the values in the column are numbers, the Number Filters option will change to Text Filters
In the Top 10 AutoFilter window, select Bottom from the first drop down list.
In the second box, type 5, or use the arrows to change the number to 5
In the third drop down box, select Items, then click OK.

Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.

refer the link

http://www.contextures.com/xlautofilter01.html

Pichart Y. replied to Rowland Hamilton on 06-Aug-11 01:48 AM

Hi Rowland,

I go through Ron's code, but I face the problem of debug, don't know why, then I design this for you...

if you want to have more than 1 fields to filter, we should use the concept of Array( ) again, then loop in the previous array...if don't clear please ask again

Try this code...

please adjust the range according to your data

Here I try with my data,

- data start from range("A1:H" * LstRow)

- I want to filter the value in column B then the The field:=2

**notice this code row

For I = LBound(myArr) + 1 To UBound(myArr)

in Lbound of the array it represent the value 0, start from 0 but if we give this variable to the criteria, we will not see the array(0) of Array("*Cost*", "*cost*", "*Total*", "*total*", ""), it should be 1-5.

------------------------------------------------------------

Sub AutoFilterBySlection2()

Dim myArr As Variant

myArr = Array("*Cost*", "*cost*", "*Total*", "*total*", "")

For I = LBound(myArr) + 1 To UBound(myArr)

LstRow = Range("A65536").End(xlUp).Row

ActiveSheet.Range("$A$1:$H$" & LstRow).AutoFilter Field:=2, Criteria1:=myArr(I)

Range("a1").Select

Selection.CurrentRegion.Offset(1, 0).EntireRow.Delete

Next I

End Sub

-------------------------------------------------------------------------

Pichart Y.

Rowland Hamilton replied to Pichart Y. on 06-Aug-11 02:08 AM

Thanks, Pritchard. I'll take a look at it when I get back in the office. - Rowland

Rowland Hamilton replied to Ravi S on 06-Aug-11 02:57 AM

Greg: Thank you.

I should have been more precise that I was trying to code this in vba and allow for unknown variables (like blank rows and changing filter array size, particularly due to row count ). The recorded macro of my filter creates restraints when it comes to those kind of things.

Although I still did use the recorder to simply find the last row by:

1) Knowing y deepest row of data is in column B
2) Inserting a blank row between column A and B and giving new column B a header in bottom header row 3
3) Selecting cell C65,536 then shift end up to the last populated row and offset select -1 columns to new column B
4) Marked bottom of column B with "x" and copy/that, shift end up/selecting the range, paste offset 1 row from top
5) including this row of contiguous data in my filter array so it includes all rows of good data

Simple, quick solution, computes slower than other macros and not as robust, but it got the job done.

If I didn't know my workbook would bottom out at 65,536 rows (which it actually doesn't with Excel 2010, but I was being flexible), I could probably:

Do same steps 1) and 2) as above
New step 3a) shift end down from new cell B3 (only populated cell in new column B) and offset 1 to column C
New step 3b) shift end up from active cell and offset 1 back to column B
Do same steps 4) and 5) as above

Very easy method but there are more efficient codes that do more, faster with less lines.

Thank you, Rowland