Link to home
Start Free TrialLog in
Avatar of angelfromabove
angelfromaboveFlag for United States of America

asked on

Auto-Filter a list using a ComboBox with no Code

Hello-

Is there any way to add a "menu of options" at the top of a spreadsheet in a combobox without VBA Code? When an item in the drop-down is selected, it would also act as an auto-filter.

Thanks!
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

It's possible to create an options-like droplist using Data Validation; that is very straight-forward.

However, it's not possible to externally filter a set of data in this manner without VBA.  

-Glenn
Avatar of angelfromabove

ASKER

Hey Glenn!

It's been a while! My project has been put on hold with the Forecast Model, but I will be back to it very soon!

Ok, so in the interim, since I have to use code, I would need 2 combo boxes which would filter the spreadsheet upon selection of a value in the combo-boxes (similar to how auto-filter) works but the combo boxes look prettier and give the illusion of a visible menu:

1) !st Combo box would be using values in Column C
2) 2nd Combo box would be using values in Column I

Thanks!
Hello again!  It has been a while.

Regarding this request:
1) Will there need to be an option to select more than one value in these combo boxes?
2) I'd the content in columns C & I distinct or are there multiple occurrences of values?

An example workbook will be helpful, if possible.

Regards,
-Glenn
Sent from my Windows Phone
My responses are:

1) No, I only need one value at a time, but I guess I would need an "All" button when no values are selected.
2)There are multiple occurences of the same values in each column, but in the menu box, I would only need one occurence of each value (just like auto-filter)
3) In column C I have 2 unique values and in Column I, there are 10 unique values. (That could change, but I just wanted to give you an idea)

Thanks!

Oh, my apologies, I'm so swamped, I truly didn't have time to mock up the data for a sample workbook because because I'm on a time constraint and I thought this would be straight forward, but let me know if you absolutely need one.

Cheers!
Here is a preliminary workbook for you to review.  I haven't included the logic to auto-populate the data validation boxes on top, but will follow up with that after making any changes you need to this.

Again, this requires VBA.  There is a Worksheet_Change event in the main sheet that checks to see if the values for either selection box have been changed and then applies filtering as necessary.  The boxes can be moved anywhere, but obviously should stay above the data table.

Regards,
-Glenn
EE-ComboFilter.xlsm
I've added new code that will automatically update the data validation lists for the two filter boxes and am attaching a revised version of the workbook.

-Glenn
PS  Thanks for dlmille for his QSort subroutine allowing the sorting of values in an array.  I used that here.
EE-ComboFilter.xlsm
Perfect, this is exactly what I need! What rows of code do I need to amend when I place this in my code? When I get that answer, I will give you your points and be on my way.:-)

Thanks again!
Have you looked at the Slicer Option? Not as aesthetically pleasing as a Combo Box but does the job without the requirement for VBA (as requested).

Thanks
Rob H
Thanks Rob-

Great suggestion, but I have a specific request for the aesthetically pleasing Combo Box. Plus, I thought the slicer only works with Pivot Tables or Power Pivot and I didn't want to go that route. Glenn's solution works for me.
A -

I've annotated the example workbook with comments that highlight all the variable names that you need to change to use this code in your own workbook.  All this code should be added to the same sheet object that the table is in.  Do a search (in VBA) for "CHANGE" and you will see each line.

Also, I used an Excel Table in this example file (name: "Table1") so I wouldn't have to enter a range.  If your data is not in an Excel table, but at least has a definitive width in columns you can replace "Table1" with something like "$A$6:$I$10000" (using some large number for maximum rows) and that will work.

-Glenn
EE-ComboFilter-b.xlsm
Rob has a good point:  If you have Excel 2013, Slicers are available for Excel Tables.

It took me a while to use Slicers with PivotTables, but I'm warming up to them now, even redesigning some of my existing PTs at work to use them.  I do like seeing value options all the time, especially for PT filter values.
Thanks Glenn -

I am familiar with them and use them regularly for Pivot tables.  They are wonderful! This is for a different purpose though, and the users don't want this in a pivot table format unfortunately. Thanks for the file with the commentary, I will amend my file and get back to you soon.
Glenn -

I followed the instructions, pasted the code in, changed the items that needed changing. Then I went back to the Worksheet and added my own Combo Box, and linked it to Cell C1 and it's not displaying any values??
I apologize; there are two lines in the Worksheet_Change event I forgot to highlight:
Private Sub Worksheet_Change(ByVal Target As Range)
    'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
    If Target.Address = "$C$2" Then 'CHANGE: $C$2 to actual location of the droplist cell that will filter column C
        If Target.Value = "All" Then
            Range("A6").Select 'CHANGE A6 to top-left cell in table
            ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
        Else
            ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=Range("C2").Value 'CHANGE C2 to actual location of droplist cell that will filter column C
        End If
        Range("C2").Select
    ElseIf Target.Address = "$I$2" Then  'CHANGE: $I$2 to actual location of the droplist cell that will filter column I
        If Target.Value = "All" Then
            Range("A6").Select 'CHANGE A6 to top-left cell in table
            ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9
        Else
            ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:=Range("I2").Value  'CHANGE I2 to actual location of droplist cell that will filter column I
        End If
        Range("I2").Select
    End If
End Sub

Open in new window


Change Lines 8 and 16.  Hopefully that should fix it.

-Glenn
Nope, I already found those lines and changed everything. Do I need to add my own Combo Boxes, or does the macro do it?
ComboBoxes:  You shouldn't have to do anything; the code assumes two locations for the filtering of columns C and I.  My example used C2 and I2; you can have any two you want, but they should be on the same sheet as the data (above the data is best) and have to be consistently referenced in the code.
Yes, I did that, I used C1 and I1, above the data.  Do I have to insert the boxes via Developer and an Active X control is what I'm asking?
Here's what I amended the code to: Maybe you will see something I'm not seeing.


Private Sub Worksheet_Activate()
    'Re-builds the two data-validation filters based on the data in columns C & I
    Dim Dic As Object
    Dim rng As Variant
    Dim x As Long
    Dim arrUList As Variant
    Dim strDVList As String
    'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
    ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter

    'Column C
    rng = Range("C3", Range("C3").End(xlDown)) 'CHANGE: C7 to first row of data in column C
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.compareMode = vbTextCompare
    For x = 1 To UBound(rng, 1)
        Dic.Item(rng(x, 1)) = rng(x, 1)
    Next x
    'assign Dic to array then insert in cells
    arrUList = Dic.items
    Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
    For x = 0 To UBound(arrUList)
        strDVList = strDVList & "," & arrUList(x)
    Next x
    strDVList = "All" & strDVList
    Range("C1").Select 'CHANGE: C2 to actual location of the droplist cell that will filter column C
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=strDVList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
   
    'Column I
    strDVList = ""
    arrUList = ""
    rng = Range("I3", Range("I3").End(xlDown)) 'CHANGE: I7 to first row of data in column I
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.compareMode = vbTextCompare
    For x = 1 To UBound(rng, 1)
        Dic.Item(rng(x, 1)) = rng(x, 1)
    Next x
    'assign Dic to array then insert in cells
    arrUList = Dic.items
    Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
    For x = 0 To UBound(arrUList)
        strDVList = strDVList & "," & arrUList(x)
    Next x
    strDVList = "All" & strDVList
    Range("I1").Select 'CHANGE: I2 to actual location of the droplist cell that will filter column I
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=strDVList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
    If Target.Address = "$C$1" Then 'CHANGE: $C$2 to actual location of the droplist cell that will filter column C
        If Target.Value = "All" Then
            Range("A2").Select 'CHANGE A6 to top-left cell in table
            ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter Field:=3
        Else
            ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter Field:=3, Criteria1:=Range("C1").Value
        End If
        Range("C1").Select
    ElseIf Target.Address = "$I$1" Then  'CHANGE: $I$2 to actual location of the droplist cell that will filter column I
        If Target.Value = "All" Then
            Range("A2").Select 'CHANGE A6 to top-left cell in table
            ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter Field:=9
        Else
            ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter Field:=9, Criteria1:=Range("I1").Value
        End If
        Range("I1").Select
    End If
End Sub
Sub QSort(sortArray As Variant, ByVal leftIndex As Integer, ByVal rightIndex As Integer)
    'Code by EE expert, dlmille
    'See https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_5062-Drop-Down-List-with-Unique-Distinct-Values-enhancing-the-ComboBox-with-a-few-steps-and-a-little-code.html
   
    Dim compValue As Variant
    Dim i As Integer
    Dim J As Integer
    Dim tempVar As Variant

    i = leftIndex
    J = rightIndex
    compValue = sortArray(Int((i + J) / 2))
    Do
        Do While (sortArray(i) < compValue And i < rightIndex)
            i = i + 1
        Loop
        Do While (compValue < sortArray(J) And J > leftIndex)
            J = J - 1
        Loop
        If i <= J Then
            tempVar = sortArray(i)
            sortArray(i) = sortArray(J)
            sortArray(J) = tempVar
            i = i + 1
            J = J - 1
        End If
    Loop While i <= J

    If leftIndex < J Then QSort sortArray, leftIndex, J
    If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub
No; my example uses no ActiveX or form controls.  It's all done in-sheet.

Okay, your controls are in C1 and I1.  Can you specify the data range?  Also, note that I used an Excel Table ("Table1"); if you're not using an Excel Table, that name will have to be replaced in the subroutines.

-Glenn
Okay, this line in the Worksheet_Activate code has to be changed from:
ActiveSheet.ListObjects("$A$2:$o$10000").Range.AutoFilter
to
ActiveSheet.Range("$A$2:$O$10000").AutoFilter

and all similar lines that originally referenced the Excel Table object.  Sorry for the oversight.

I've updated your code and put it on the Sheet2 object in the attached workbook.  Sheet2 should mimic your layout so you can test.  If it looks good, just copy ALL the code from the Sheet2 object and replace yours.

-Glenn
EE-ComboFilter-b.xlsm
Ahhh nothing after I pasted Sheet 2 in my workbook. I checked again and my data is formatted as a table (not a pivot table), Would that make a difference?  I'm not getting any error messages, just no boxes are appearing. :-(
Dangit.  :-)

If your data is an Excel Table, then it has a name assigned (check Menu:  Formulas, Name Manager - see if there is a "Tablex" name listed).  If it does, then my original code would be the way to go (see the code in Sheet1).  

If you click on Cells C1 or I1, do you not see a data validation drop arrow appear?  That's what should happen, but only if you select the cell.

-Glenn
Very sorry,  It says it's called Table1 and here's how it's defined. =Sheet1!$A$1:$P$1005.  I do not see a data validation drop-down arrow. The cell is blank.

Would you mind changing the code once more and attaching a new worksheet if it was the Table1 name causing me to be off?

My apologies again.
There is problem here.  You have the table defined starting in row 1 ($A$1...) and the two filtering cells are also in row 1 (C1 and I1).  You can't do this; it affects the header row which is required for an Excel Table.  This may also be why filtering isn't happening.

I've re-defined the example data on Sheet2 as an Excel Table - named "Table2" that covers $A$3:$P$1005 (actually A2, but header row is defined).  And I updated the Worksheet_Change and Worksheet_Activate codes accordingly.

-Glenn
EE-ComboFilter-c.xlsm
And, I have to ask a possibly overly-simplistic question, but are you Enabling content? That is, activating macros?
-Glenn
1) Of course Enabling Content.
2) My headers are actually in A2, so is that why you are starting it in A3?
1) okay.  whew.
2) If you look at the range defined for "Table2" is literally shows $A$3:$P$1005, even though my headers are also in row 2.  That's just how Excel sets up the range.  So, no difference here.

So, my workbook behaves as you think it should?  That is, when you view "Sheet2" you see a table with two colored cells on top and if you click on either cell, a drop list appears letting you select any of the values in that column?

I really don't know where else to go with this short of seeing the actual workbook.  Sorry.

-Glenn
Yes, your table performs perfectly as expected. I changed the code to Table1 in my file and my file looks exactly like yours. I have no idea, but still nothing, uggh.?

Is there something that triggers the macro, like hitting enter or updating a cell or something, or does it just run open opening the workbook? I don't see anything else wrong unless I don't have some of the code that you used in my library, but that would give me an error, and it didn't.
GOOD NEWS!, it seems to be working now, I PRAYED,  closed it, saved it and reopened it and took it off of the Sharepoint Site. The bad news is that it's  not showing all of the values in the Row. For example, I have the values of "S" and "N" in Column C. The drop-down is only showing "S" and "All".  The "S" values stop at row 25 and the "N" values begin on Row 26. It seems like it's not going down the whole column or something???
My drop-down data-validation list appears to be truncated. It's not picking up all of the values in the column, nor picking up new values when a value is changed. I looked at the Data Validation settings on the menu, and it didn't show all of the values. I manually added them to my Data Validation drop-down list and it worked, but I shouldn't have to do that right?
ok, I just checked your spreadsheet and it doesn't auto udpate values either. I have to go into the VBA code and click Run Code in order for the values to update in the Data Validation. Can we fix that, so users don't have to hit run to trigger the macro?
I really don't know what to say.  If my example spreadsheet does not work for you, then there is some environment difference on your machine.  

Going back to your post (40374244): the two macros are triggered by specific actions in Excel:
Worksheet_Activate - happens whenever the sheet is selected to view.

Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.


I would have thought that perhaps you had added the Worksheet_Change / Activate events (code) to a module or another sheet by accident, but since you state that the example workbook does not run automatically, I'm at a loss as to how to assist.




Sorry,
-Glenn
I didn't say it didn't work, we are almost there. I was just saying it doesn't work instantly and was trying to work with you to troubleshoot the reasons why.  Can you please explain further what you mean by your statement below?

"Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.

I guess I'm not sure what you mean by it is bypassed?

And please don't take my questions personally, I'm just trying to get it to work as expected and I'm being transparent as to what I'm seeing. I changed the cell values in two of the rows, and I had to click away from the spreadsheet and go back to it in order for the macro to run, I am just wondering if the macro can be further tweaked to be triggered by anytime someone hits enter after they change any cell, so it's more automatic. If you can't amend it that way, I will have to give you the points for what you did and open it up again. Because if new values are not automatically showing up in the Data Validation list, it is not working for me.

Thanks again.
Worksheet_Change:  I coded that so it would only run if there was a change to the values in cells C1 or I1.  This limits it to only changing the filters on the table when either of those cells are changed.  If any other cell is changed, nothing happens.
------------------------------------------------------
You are asking for functionality beyond the scope of the original question.
Is there any way to add a "menu of options" at the top of a spreadsheet in a combobox without VBA Code? When an item in the drop-down is selected, it would also act as an auto-filter.

As I pointed out, that was not possible without VBA Code.  Even a ComboBox form control with the unique data values for a given column inserted (say, via an array function on a separate sheet) will not act as an AutoFilter control on a data table.

However, I was able to show by example how one could create an external cell that would 1) have a data validation list of the unique values of a given column and 2) control the filtering of that column using VBA (with a Worksheet_Change event).

It now sounds like you want the filters to be activated/refreshed whenever new data is added to the table.  That is possible, but problematic.  If data is added that is not within the filter specifications and an automatic update (like Worksheet_Change) is in effect, then that data row would disappear (i.e., be hidden) immediately upon entry - likely halting further entry.  This would happen if a new value was added to column C or I that didn't already exist and would not update the data validation list either.

The only way that I can think of to make this happen is to include new features (form buttons) that would either "Refresh" or "Reset" the filtering on the data.  This would allow the user to remain on the sheet, change/add/remove data as-needed and then re-apply the visible filters again.  Or, just turn off all filters at once (in essence, setting both filters to "All").

If you think my example file and code does not meet the original request, I understand and will defer to other experts to provide a workable solution for you.  But, if it does satisfy the scope of your original question and you would like to enhance that, please open a new question with the new behavior you want to see.  Feel free to use my example (or your modified copy) as a basis; that will be very helpful to EE experts.

-Glenn
Thank you.

I apologize for the misunderstanding and if you didn't have clarity on my original ask, but I am absolutely not asking for functionality beyond the original scope of the question. Part of my explanation states clearly that I wanted the combo box to act similarly to an auto-filter. That seems pretty straight forward to me, I am not enhancing anything, just trying to help you to understand what the ask is in subsequent posts.  Obviously, when values change in the Columns that I wanted filtered (C and I) the, combo box or whatever you added should accommodate those values. That's how auto-filter works.  There would be no other reasons the values in C1 or I1 would change unless they changed in the underlying spreadsheet, so I'm having trouble understanding what you mean when you say you coded it to change only when those values change in C1 or I1?

Anyway, I had a similar issue which was done for me a while back by one of the experts for a different workbook, so here's the Solution number:
Filter a worksheet using a combo or list box without VBA Code?
Zack Barresse2013-09-21 at 19:29:24ID: 39512358

I've also pasted the code below: Would it help you to look at this or should I just engage another Expert?

Please advise,
Thank you,
Option Explicit

'DATABANK SHEET
Private Const m_NEW             As String = "A2:q"

'REPORT destination
Private Const m_Dest_All        As String = "6:193"
Private Const m_Dest_NEW        As String = "A7:l16"
Private Const m_Dest_RELO       As String = "A19:l38"
Private Const m_Dest_RENO       As String = "A41:l65"
Private Const m_Dest_REFRESH    As String = "A68:l117"
Private Const m_Dest_CLOSURE    As String = "A120:l188"
Private Const m_Dest_CONSOLIDATION As String = "A192:l193"

Private Abort                   As Boolean

Private Sub ComboBox1_Change()

    Dim wsTemp                  As Worksheet
    Dim wsData                  As Worksheet
    Dim iLastRow                As Long
    Dim iLastData               As Long

    Static Abort1               As Boolean

    If Abort Then GoTo ExitCombo1
    If Abort1 Then GoTo ExitCombo1
    Abort1 = True
    Abort = Abort1

    '/// Set variables
    Set wsData = ThisWorkbook.Worksheets("DATABANK")


    '////////////////////////////////////////////////////////////////////////////////
    '/// NEW
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "NEW" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=NEW" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_NEW).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_NEW, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\



    '////////////////////////////////////////////////////////////////////////////////
    '/// RELO
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REL" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=RELO" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_RELO).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_RELO, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    '////////////////////////////////////////////////////////////////////////////////
    '/// RENO
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REN" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=RENO" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_RENO).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_RENO, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    '////////////////////////////////////////////////////////////////////////////////
    '/// REFRESH
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REF" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=REFRESH" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_REFRESH).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_REFRESH, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    '////////////////////////////////////////////////////////////////////////////////
    '/// CLOSURE
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "CLO" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=CLOSURE" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_CLOSURE).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_CLOSURE, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    '////////////////////////////////////////////////////////////////////////////////
    '/// CONSOLIDATION
    '////////////////////////////////////////////////////////////////////////////////
    '/// Get data
    '    If Me.ComboBox2.Value = "ALL" Then
    wsData.Range("Headers").Offset(1, 0).Resize(wsData.Rows.Count - 10, 1).Clear
    wsData.Range("Criteria_Type").Formula = "=" & Chr(34) & "=CONSOLIDATION" & Chr(34)
    iLastData = wsData.Cells(wsData.Rows.Count, "B").End(xlUp).Row
    wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Range("Criteria"), CopyToRange:=wsData.Range("Headers"), Unique:=False
    iLastRow = wsData.Cells(wsData.Rows.Count, wsData.Range("Headers").Column).End(xlUp).Row
    '/// Clear REPORT sheet
    Me.Range(m_Dest_CONSOLIDATION).ClearContents
    '/// Ensure data was returned
    If iLastRow > 2 Then
        '/// Place new data
        Me.Range(Split(m_Dest_CONSOLIDATION, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
    End If
    '    End If
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    Abort1 = False
    Abort = False

    Me.Activate
    Me.Range("A1").Activate
ExitCombo1:
    Application.EnableEvents = True

End Sub

Private Sub ComboBox2_Click()
    Static UpdatedValue         As String
    Static Abort2               As Boolean

    Abort = True
    If Abort2 Then GoTo ExitCombo2
    Abort2 = True

    Application.EnableEvents = False
    On Error GoTo ExitCombo2
    Me.Range(m_Dest_All).RowHeight = 0
    Select Case Me.ComboBox2.Value
    Case "ALL"
        Me.Cells.EntireRow.AutoFit
    Case "NEW"
        Me.Range(m_Dest_NEW)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_NEW).Rows.Count + 2, Me.Range(m_Dest_NEW).Columns.Count).EntireRow.AutoFit
    Case "REL"
        Me.Range(m_Dest_RELO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_RELO).Rows.Count + 2, Me.Range(m_Dest_RELO).Columns.Count).EntireRow.AutoFit
    Case "REN"
        Me.Range(m_Dest_RENO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_RENO).Rows.Count + 2, Me.Range(m_Dest_RENO).Columns.Count).EntireRow.AutoFit
    Case "REF"
        Me.Range(m_Dest_REFRESH)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_REFRESH).Rows.Count + 2, Me.Range(m_Dest_REFRESH).Columns.Count).EntireRow.AutoFit
    Case "CLO"
        Me.Range(m_Dest_CLOSURE)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_CLOSURE).Rows.Count + 2, Me.Range(m_Dest_CLOSURE).Columns.Count).EntireRow.AutoFit
        '    Case "CON"
        '        Me.Range(m_Dest_CONSOLIDATION)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_CONSOLIDATION).Rows.Count + 2, Me.Range(m_Dest_CONSOLIDATION).Columns.Count).EntireRow.AutoFit
    End Select

    Abort2 = False
    Abort = False

    Range("A1").Select
ExitCombo2:
    Application.EnableEvents = True
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glenn,

Thanks for all of your help! I will contact Zack at your suggestion.