Sunday, July 20, 2008

Excel Filter Show All using VBA

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

See also:

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

4 comments:

  1. This macro needed to be re-written for several reasons:

    Sub 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

    ReplyDelete
  2. Sorry to post twice - even that improvement could use some more.

    This 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

    ReplyDelete
  3. this excel Vba does not work !

    ReplyDelete
  4. Don'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.

    Sub 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

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group