AutoFilter applies a filter to a range from code, by field number and criteria. Combined with SpecialCells(xlCellTypeVisible) it processes only matching rows.

Code

Range("A1").AutoFilter Field:=3, Criteria1:="West"

• Filter one value

  Range("A1").AutoFilter Field:=3, Criteria1:="West" → Filters the table starting at A1 to West in column 3

  One condition.

• Range condition

  Range("A1").AutoFilter Field:=5, Criteria1:=">100", Operator:=xlAnd, Criteria2:="<500" → Between 100 and 500

  Two conditions on one field.

• Copy filtered rows

  Range("A1").CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Copy Range("H1") → Copies only the visible rows

  Extract filtered data.

• Field counts from the first column of the filtered range, not from column A.
• ActiveSheet.AutoFilterMode = False clears the filter.
• ShowAllData clears criteria but keeps the buttons.

• SpecialCells errors when nothing is visible; handle it.
• Criteria for dates need the right format or serial.