Newbie : Autofilter thru code ?

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I am using Excel 97.

I am getting started with Excel VBA coding. I wanted to now is it
possible to apply an Autofilter with VBA coding. The reason I asked is
because when I tried to record a key stroke macro with an autofilter,
the spreadsheet did not record something that I can use.

So what lines a code do I need to filter data.
 
Rich,

Excel doesn't record key stroke macros. I builds VBA, a mix of the old
BASIC language, and the Excel object model (which gives you access to all
kinds of Excel stuff, like cells, sheets, operations (methods)).

These two are essentially equivalent. They turn on Autofilter for the range
(which gets expanded) A1:

Range("A1").Select
Selection.AutoFilter

Range("A1").Autofilter
 
Here's an example that I use at work

Sub Area_Code()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Area Code")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=4, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub

I have this attached to a forms puch button when I want to filter on area
code,
the above use an input box where I type the area code

Obviosuly you won't need that, you could use

Sub FilterMe()
Application.ScreenUpdating = False
Selection.AutoFilter Field:=1, Criteria1:="10"
Application.ScreenUpdating = True
End Sub

This will filter on the first column, using 10 as crieria.

To reset w/o turning off the filter you could use

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
Back
Top