Solved

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

Posted on 2010-09-08
3
230 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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