Solved

When Excel VBA uses AutoFilter, extra rows are hidden after filter

Posted on 2010-09-08
3
233 Views
Last Modified: 2012-05-10
I've got VBA code that filters a portion of data (on sheet tab "Equipment Worksheet") after selections are made on a different sheet ("SW Worksheet, cells H251:H254). Everything works great EXCEPT that the filtering, which is supposed to end at row 164 on "Equipment Worksheet", actually hides everything through row 192, and I can't figure out how to only make it hide through row 164.

The file is attached: a selection has already been made on the "SW Worksheet;" to run the code, click the gray button at cell D68 on "Equipment Worksheet."

I appreciate any ideas!
Sub PickCoffee()

    Application.ScreenUpdating = False

    Dim br As String                'Brew Prep Area

    Dim barLen As String            'Bar Length

    Dim vol As String               'Volume

    Dim s As String                 'sink

    

    Sheets("SW Worksheet").Select

    'set barLen

    If IsEmpty(Range("H252")) Then

        MsgBox "Please select coffee bar length."

        Range("H252").Select

        Exit Sub

    Else

        barLen = Range("H252").Value

    End If

    

    'set s

    If IsEmpty(Range("H254")) Then

        s = "x"                     'no sink criteria will be used in filter

    ElseIf Range("H254").Value = "None" Then

        s = ""

    Else

        s = Range("H254").Value

    End If

    

    'set br

    If IsEmpty(Range("H251")) Then

        br = "x"                    'no brew prep criteria will be used in filter

    ElseIf Range("H251") = "back wall" Then

        br = ""

    Else

        br = "Y"

    End If

    

    'set vol

    If IsEmpty(Range("H253")) Then

        vol = "x"

    Else

        Select Case Range("H253").Value

            Case "Low"

                vol = "LV"

            Case "Med/High"

                vol = "MV/HV"

            Case "High"

                vol = "HV"

            Case "Extremely High"

                vol = "EV"

            Case Else

                MsgBox "Vol is " & Range("H253").Value

        End Select

    End If

    

    'If AutoFilter already on and filtered, then unfilter

    Worksheets("Equipment Worksheet").Activate

    If ActiveSheet.FilterMode Then

        Range("J70").Select

        ActiveSheet.ShowAllData

    Else

        Range("G69:R164").Select

        Selection.AutoFilter

        Range("G70").Select

    End If

    

    'now filter data with given data

    'bar length

    Selection.AutoFilter Field:=7, Criteria1:=barLen

    

    'brew prep area

    If br = "x" Then

        Selection.AutoFilter Field:=12

    Else

        'ActiveSheet.Range("$G$69:$R$164").AutoFilter Field:=12, Criteria1:=br

        Selection.AutoFilter Field:=12, Criteria1:=br

    End If

    

    'volume

    If vol = "x" Then

        Selection.AutoFilter Field:=8

    Else

        Selection.AutoFilter Field:=8, Criteria1:=vol

    End If

    

    'sink

    If s = "x" Then

        Selection.AutoFilter Field:=9

    Else

        Selection.AutoFilter Field:=9, Criteria1:=s

    End If

    

    ActiveSheet.Range("D70").Select

    Application.ScreenUpdating = True

    MsgBox "Based on choices made in cells H251 - H254 on the SW Worksheet," & _

        vbCrLf & "your options are now visible here." & _

        vbCrLf & vbCrLf _

        & "If no options appear here, change your choices on the SW Worksheet."

        

End Sub

Open in new window

SOW-for-EE.xls
0
Comment
Question by:nbozzy
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33631116
Make row 165 blank. :)
0
 

Author Comment

by:nbozzy
ID: 33631225
Shoot me! You know, rorya, I actually did have it blank at one point, but I changed my code since then. Silly me.....thanks so much!
0
 

Author Closing Comment

by:nbozzy
ID: 33631234
Glad you were there for me!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now