For Loop Over Multiple Ranges

[J_Raab]

Hello all, new to the forum but not to the site. Found many an answer or direction to start when dealing with my macros. So thanks for that. Unfortunately my current issue is above my skills and I can't find a forum that helps me find the answer, or I'm just not searching for it properly. I'm handling a training retention document and I'm trying to make it searchable using drop down menus. I'm trying to figure out how to get a For loop to go through multiple ranges and hide/unhide columns based on it's findings, I've found forums discussing Application.Union but my skills aren't up to the task, and I'm not sure that's what would fix my issue. I have two sets of data; my dropdowns are Divisions (DivS) and Status (StatS) and the corresponding ranges they search are Div and Stat. My code works, except it only displays the last segment of code run, in this case Status. So if I change the Division from Florida to Carolina, it'll only display the columns that match the Status Criteria (Either Active or Inactive). Can anyone advise me on how I can modify the code to search both Data Ranges (Div and Stat) using their corresponding Search Criteria (DivS and StatS) and display or hide the column if BOTH criteria are met. I've done my best to give an explanation of each line in the code as well. Thanks


Sub Sort()


Application.ScreenUpdating = False


'Division Ranges


Dim Div As Range
Set Div = Range("F9:BEW9")


Dim DivS As Range
Set DivS = Range("E1")


'Status Ranges


Dim Stat As Range
Set Stat = Range("F10:BEW10")


Dim StatS As Range
Set StatS = Range("E2")




For Each Cell In Div
'Hide column if cell value is blank
If Cell.Value = "" Then
Cell.EntireColumn.Hidden = True
'Unhide column if Division Search value equals "All" and cell value isn't blank
ElseIf DivS.Value = "All" And Cell.Value <> "" Then
Cell.EntireColumn.Hidden = False
'Hide Column if search value does not equal "All" but Cell value does not equal search value
ElseIf DivS.Value <> "All" And Cell.Value <> DivS.Value Then
Cell.EntireColumn.Hidden = True
'Unhide Column if Search Value and Cell Value are equal and Cell value isn't blank
ElseIf DivS.Value = Cell.Value And Cell.Value <> "" Then
Cell.EntireColumn.Hidden = False
End If
Next Cell


For Each Cell In Stat
'Hide column if cell value is blank
If Cell.Value = "" Then
Cell.EntireColumn.Hidden = True
'Unhide column if Status Search value equals "All" and cell value isn't blank
ElseIf StatS.Value = "All" And Cell.Value <> "" Then
Cell.EntireColumn.Hidden = False
'Hide Column if Status search value does not equal "All" but Cell value does not equal search value
ElseIf StatS.Value <> "All" And Cell.Value <> StatS.Value Then
Cell.EntireColumn.Hidden = True
'Unhide Column if Status Search Value and Cell Value are equal and Cell value isn't blank
ElseIf StatS.Value = Cell.Value And Cell.Value <> "" Then
Cell.EntireColumn.Hidden = False
End If
Next Cell


Application.ScreenUpdating = True


End Sub

 

[J_Raab]

Thought about that, but there's simply to much information to allow a worksheetchange macro to run. I'm looking at around 1500 employees with 100 separate training topics, giving me 150,000 cells of unique data that's pulled from a database and then hidden/unhidden based on the data in 2 rows (3000 unique data points) after being compared against 2 user selected cells. Plus, it hides/unhides rows based on the users selection of "All" or a specific training topic. It's currently running at 25 seconds to complete the macro once the user chooses their options and executes the macro using my button control. I've got application.screenupdating running to bring down the runtime, but it's still a bit long at 25 seconds, so to do it after EACH selection change, would take several minutes just to get to the data the user needs, which is to long. Really appreciate the advice though