Shortcut to show all after filtering?

  • Thread starter Thread starter Roger Dodger
  • Start date Start date
R

Roger Dodger

Is there a shortcut to getting the filter boxes to show all? I am creating a
series of macros to run a combination of filters. When I'm finished I am
left with a partial amount of data. Currently I have to manually show all in
each column before I start a new filter sequence. Can this be done a better
and faster way?

Thanks in advance
Kevin
 
' Keyboard Shortcut: Ctrl+f
'
' Go to the heading row:
Range("A1").Select
If Selection.AutoFilter Then Selection.AutoFilter
 
If you're using xl2003 or below, you could add an icon to your favorite toolbar.

Tools|Customize|Commands tab|Data Category
And drag "Show All" and maybe "Autofilter" to that favorite toolbar.

xl2007+ has the filter button on the ribbon under the Data group (if I remember
correctly).

In code, you could use something like:

dim wks as worksheet
set wks = worksheets("Somesheetname")
with wks
'show all the data
If .FilterMode Then
.ShowAllData
End If
end with
 
If you're using xl2003 or below, you could add an icon to your favorite toolbar.

Tools|Customize|Commands tab|Data Category
And drag "Show All" and maybe "Autofilter" to that favorite toolbar.

xl2007+ has the filter button on the ribbon under the Data group (if I remember
correctly).

In code, you could use something like:

dim wks as worksheet
set wks = worksheets("Somesheetname")
with wks
    'show all the data
    If .FilterMode Then
       .ShowAllData
    End If
end with
Click to expand...

You could always just unfilter when finished with the filter>copy. Use
the range. Here is one I did for a client to filter>copy to another
location and unfilter.

With ss.Range("a1:z" & lr)
For i = 1 To Cells(3, Columns.Count).End(xlToLeft).Column
.AutoFilter Field:=2, Criteria1:=Cells(1, 1)
.AutoFilter Field:=3, Criteria1:=Cells(3, i)
ss.Range("a2").Resize(lr).Copy Cells(4, i)
Next i
..AutoFilter
End With
 
Back
Top