Show All Information from a Filtered Range
Sub Show_All_In_AutoFilter()
Dim oWS As Worksheet ' Worksheet Object
On Error GoTo Disp_Error
' ---------------------------------------------
' Coded by Shasur for www.vbadud.blogspot.com
' ---------------------------------------------
oWS = ActiveSheet
oWS.ShowAllData()
If Not oWS Is Nothing Then oWS = Nothing
' --------------------
' Error Handling
' --------------------
Disp_Error:
If Err <> 0 Then
MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")
Resume Next
End If
End Sub
Set AutoFilter to all using Excel VBA
Create AutoFilter with Multiple Criteria using Excel VBA
AutoFilter using Excel VBA
Check for existence of Filter using Excel VBA
Excel Filter Show All using VBA
Retrieve / Get First Row of Excel AutoFilter using VBA
Posts
This macro needed to be re-written for several reasons:
ReplyDeleteSub FilterShowAll()
Err.Clear
On Error GoTo Disp_Error
If ActiveWorkbook Is Nothing Then
Exit Sub
End If
ActiveWorkbook.ActiveSheet.ShowAllData
Disp_Error:
If Err.Number <> 0 Then
Call MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Sub FilterShowAll()")
Resume Next
End If
End Sub
Sorry to post twice - even that improvement could use some more.
ReplyDeleteThis is my "silent" version that will run with filters present or not, on or not:
Sub FilterShowAll()
' ---------------------------------------------
' 1/18/10 Wayne Erfling: Initial release - rewrote poor example from Internet
' ---------------------------------------------
If ActiveWorkbook Is Nothing Then
Exit Sub
End If
If ActiveWorkbook.ActiveSheet Is Nothing Then
Exit Sub
End If
If ActiveWorkbook.ActiveSheet.AutoFilter Is Nothing Then
Exit Sub
End If
If ActiveWorkbook.ActiveSheet.AutoFilter.FilterMode Then
ActiveWorkbook.ActiveSheet.ShowAllData
End If
End Sub
this excel Vba does not work !
ReplyDeleteDon't know if shera was referring to the original code or my code, but I took a little while to make my version work with Excel 2003 as well as Excel 2007; tested both today (3/7/10). May need some line breaks re-instated.
ReplyDeleteSub FilterShowAll()
' Is an autofilter is in place, show all rows, otherwise exit silently.
' ---------------------------------------------
' 3/ 7/10 Wayne Erfling: Adapt to work on Office 2003
' (and possibly earlier - I don't know how much so)
' 1/18/10 Wayne Erfling: Initial release
' ---------------------------------------------
If ActiveWorkbook Is Nothing Then
Exit Sub
End If
If ActiveWorkbook.ActiveSheet Is Nothing Then
Exit Sub
End If
If ActiveWorkbook.ActiveSheet.AutoFilter Is Nothing Then
Exit Sub
End If
If CInt(Application.Version) < 12 Then
Dim ix, bFilterMode
bFilterMode = False
For ix = 1 To ActiveWorkbook.ActiveSheet.AutoFilter.Filters.Count
If ActiveWorkbook.ActiveSheet.AutoFilter.Filters(ix).On Then
bFilterMode = True
ix = ActiveWorkbook.ActiveSheet.AutoFilter.Filters.Count + 1
End If
Next
If bFilterMode Then
ActiveWorkbook.ActiveSheet.ShowAllData
End If
Else
If ActiveWorkbook.ActiveSheet.AutoFilter.FilterMode Then
ActiveWorkbook.ActiveSheet.ShowAllData
End If
End If
End Sub