AutoFilter un-check boxes in filter

elawton

New Member
Joined
Nov 18, 2015
Messages
12
I am using the record macro feature to write the VB code so that I can delete the rows that contain values other than "55" or "56". What I want to do is deselect "55" and "56" (notice they are not displayed) and select every other value.

Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$20734").AutoFilter Field:=1, Criteria1:=Array( _
"0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "22", _
"23", "24", "25", "26", "27", "28", "29", "3", "30", "31", "32", "33", "34", "35", "36", "39", _
"40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "5", "52", "53", "54", "59", "6", _
"61", "62", "63", "64", "65", "66", "67", "68", "69", "7", "71", "72", "73", "74", "75", "76", _
"78", "79", "82", "83", "84", "85", "86", "87", "88", "89", "9", "90", "91", "94", "96", "97", _
"98"), Operator:=xlFilterValues
Rows("2:2").Select

I tried to modify the auto generated code (see below) but get the error "AutoFilter method of Range class failed"

Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$20734").AutoFilter Field:=1, Criteria1:= _
"<55", Operator:=x1And, Criteria2:=">56"
Rows("2:2").Select

I imagine there is a pretty simple way to do this but I can't make it work.
Thanks
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

portews

Active Member
Joined
Sep 4, 2009
Messages
303
You almost had it. You want to show everything less than 54 OR greater than 56.

Try this, based on Criteria for VBA AutoFilters. How to use AutoFilter Criteria in Excel VBA

Code:
Sub test()'http://www.ozgrid.com/VBA/autofilter-vba-criteria.htm
With ActiveSheet
    .AutoFilterMode = False
    .Range("A:A").AutoFilter
    .Range("A:A").AutoFilter Field:=1, Criteria1:="<55", _
     Operator:=[COLOR=#ff0000]xlOr[/COLOR], Criteria2:=">56"
End With
Rows("2:2").Select
End Sub
 

elawton

New Member
Joined
Nov 18, 2015
Messages
12
Bill;
Thanks, that didn't work exactly as your code is written but got me close enough to solve the problem myself.
Ed
 

Watch MrExcel Video

Forum statistics

Threads
1,095,362
Messages
5,444,022
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top