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

 

[Marcelo Branco]

Just an idea (not tested)

I think you should use just one loop and do the two comparisons at once. i.e., using Offset(1) to get the cell of the range Stat.

Something like this

For each cell in Div
    If cell = "condition1" AND cell.Offset(1) = "condition2" then
        'do something 
    ElseIf cell = "condition3" AND cell.Offset(1) = "condition4" then
        'do other thing    
    ElseIf  
        ...     
    End If
Next cell

For this purpose a Decision Table would be helpful - something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD]

Div​

[/TD]
[TD]

Stat
​

[/TD]
[TD]

Hide or Unhide?​

[/TD]
[/TR]
[TR]
[TD]

=DivS​

[/TD]
[TD]

=StatS
​

[/TD]
[TD]

Unhide​

[/TD]
[/TR]
[TR]
[TD]

=DivS​

[/TD]
[TD]

All
​

[/TD]
[TD]

Unhide​

[/TD]
[/TR]
[TR]
[TD]

All​

[/TD]
[TD]

=StatS
​

[/TD]
[TD]

Unhide​

[/TD]
[/TR]
[TR]
[TD]

=""​

[/TD]
[TD]

Any
​

[/TD]
[TD]

Hide​

[/TD]
[/TR]
[TR]
[TD]

Any​

[/TD]
[TD]

=""
​

[/TD]
[TD]

Hide​

[/TD]
[/TR]
[TR]
[TD]

etc​

[/TD]
[TD]

etc
​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

etc​

[/TD]
[TD]

etc
​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(Just an example - not sure the table is correct considering your real case)

Hope this helps

M.

 

[quarencia]

Your second loop is overriding the logic of the first loop, but I think you know that. Agree with the need for a decision table. However, I would not use if statements. I'd structure it with a select case statement like below. No need to specifically define the "unhide" cases. Default to "unhide" if no "hide" condition is met. You'll need to add as many "hide" conditions as necessary to meet your decision table.

Sub Sort()

    Dim DS As Range
    Dim col As Range
    Dim DivS As Range
    Dim StatS As Range
    Dim Div As Range
    Dim Stat As Range
    Dim h As Boolean

    Application.ScreenUpdating = False

    Set DS = Range("F:BEW")
    Set DivS = Range("E1")
    Set StatS = Range("E2")
    h = True

    For Each col In DS.Columns
        With col
            Set Div = .Rows(1)
            Set Stat = .Rows(2)
            Select Case h
                Case (h And Div = "" And Stat = ""), _
                     (h And DivS <> "All" And Div <> DivS), _
                     (h And StatS <> "All" And Stat <> StatS)
                    .EntireColumn.Hidden = True
                Case Else
                    .EntireColumn.Hidden = False
            End Select
        End With
    Next col
    Application.ScreenUpdating = True
End Sub

 

[quarencia]

The h variable is just a dummy variable to make the Select statement happy. Select Case is happy to evaluate outside variables in each Case statement as long as it has a comparison to some variable in the Select Case statement as well.

 

[J_Raab]

You both kick ***. I'll test both theories out tonight and see where they get me. The worksheet is calculation heavy as well so hopefully the simplified macro will ease the time it takes to apply the logics. I've added screenupdating and calculationmanual/automatic commands but with 1500 employees and 110 separate training topics it's a lot of data that's getting pulled. Appreciate it.

 

[quarencia]

Another thought...Select Case evaluates each Case statement in order until if finds a true condition. It does not continue to evaluate the remaining Case statements. If you know the most likely outcomes in your decision table, order your Case statements by probability of occurrence and you'll speed up code execution. That may required specifying unhide conditions which I suggested not doing in my original reply.

 

[quarencia]

And my brain keeps thinking about it in the background and adding efficiencies...

I'd just unhide the entire range and then only hide what needs to be hidden (or vice versa).

Sub Sort()

    Dim DS As Range
    Dim col As Range
    Dim DivS As Range
    Dim StatS As Range
    Dim Div As Range
    Dim Stat As Range
    Dim h As Boolean

    Application.ScreenUpdating = False

    Set DS = Range("F:BEW")
    Set DivS = Range("E1")
    Set StatS = Range("E2")
    h = True

    DS.EntireColumn.Hidden = False  'Or True if that makes more logical sense for your decision table

    For Each col In DS.Columns
        With col
            Set Div = .Rows(1)
            Set Stat = .Rows(2)
            Select Case h
                Case (h And Div = "" And Stat = ""), _
                     (h And DivS <> "All" And Div <> DivS), _
                     (h And StatS <> "All" And Stat <> StatS)
                    .EntireColumn.Hidden = True
            End Select
        End With
    Next col
    Application.ScreenUpdating = True
End Sub

 

[J_Raab]

Yeah the problem with that is you could find multiple True conditions in the file for the same column. The worksheet will ultimately be able to shrink or expand based on the users selections from the drop down menu, so they could choose a specific division and then whether or not to show the active or inactive employees WITHIN that division. So the user could select my Florida Division and then say they only wanted to see the Inactive employees. So if you go by Select Case, it'll stop running once it finds the first True statement, correct? So if the user chooses Florida Division and Inactive employees, Select Case wouldn't run the hide/unhide function based on the Inactive choice, since the first T condition was Florida division, which means Active employees would still be visible. Or have I got that wrong, I'm entirely self taught (forums and just messing around) so I'm good but my overall understanding of certain code abilities is limited.

 

[Marcelo Branco]

I'd just unhide the entire range and then only hide what needs to be hidden (or vice versa).

Good idea!

M.

 

[quarencia]

Yeah the problem with that is you could find multiple True conditions in the file for the same column. The worksheet will ultimately be able to shrink or expand based on the users selections from the drop down menu, so they could choose a specific division and then whether or not to show the active or inactive employees WITHIN that division. So the user could select my Florida Division and then say they only wanted to see the Inactive employees. So if you go by Select Case, it'll stop running once it finds the first True statement, correct? So if the user chooses Florida Division and Inactive employees, Select Case wouldn't run the hide/unhide function based on the Inactive choice, since the first T condition was Florida division, which means Active employees would still be visible. Or have I got that wrong, I'm entirely self taught (forums and just messing around) so I'm good but my overall understanding of certain code abilities is limited.

Set it up so the code above runs every time user makes a change to one of the dropdowns. Then it will hide/unhide based on the current set of selections. Just make sure you have every possible set of selections accounted for in your logic.