Skip next iteration in loop - For Each - If/Then

[J_Raab]

I've created a license tracking excel for my company where we track what state our employees are licensed in and the expiration date. Since there are are several counties that require different licensing the list is fairly long (over 100 rows). I'm trying to write a Looping VBA that will hide the rows that don't match a user-chosen variable from a drop down menu, for easier viewing. I can get it to hide the first row no problem, the issue is that each data set is 2 rows.

Employee 1 Employee 2 Employee 3 etc...
Row 1 - State/County Journeyman
Row 2 - Expiration Date 1/1/19
Row 3 - State/County Master
Row 4 - Expiration Date 1/1/18
etc.....

I want the loop to entirely skip the expiration date row if the State/County row above matches the user defined variable, leaving both rows view-able. I use the below code (minus the Offset) on another part of the form and it works no problem, but since the expiration date doesn't match the variable, the macro hides that row. Nothing I can think of makes it work.

Can anyone help me with the code. Either have it skip the next iteration of the loop entirely, or give me another way of doing it where I won't need to spell out each State/County row as a separate defined range so the code only runs through those (that's going to be a massive pain)

- I put the Offset in as a placeholder more to show where I want the loop to skip (it doesn't work for my needs, least not how I wrote it)

Dim LicLoc As Range
Set LicLoc = Range("D7:D109") - Range of data sets (each set is 2 rows - 1 State/County 1 Expiration date)


Dim LicLocS As Range
Set LicLocS = Range("B5") - User defined variable from drop-down menu


For Each Cell In LicLoc
If Cell.Value <> LicLocS.Value And LicLocS.Value <> "All" Then
Cell.EntireRow.Hidden = True
ElseIf Cell.Value = LicLocS.Value Then
Cell.EntireRow.Hidden = False
Cell.Offset(1).EntireRow.Hidden = False - This is where I need the second row to stay visible
ElseIf LicLocS.Value = "All" Then
Cell.EntireRow.Hidden = False
End If
Next Cell

I appreciate any assistance.

 

[Akuini]

Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1092080a()

[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092080-skip-next-iteration-loop-each-if-then.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] LicLoc [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Set[/COLOR] LicLoc = Range([COLOR=brown]"D7:D109"[/COLOR]) [I][COLOR=seagreen]'- Range of data sets (each set is 2 rows - 1 State/County 1 Expiration date)[/COLOR][/I]


[COLOR=Royalblue]Dim[/COLOR] LicLocS [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Set[/COLOR] LicLocS = Range([COLOR=brown]"B5"[/COLOR]) [I][COLOR=seagreen]'- User defined variable from drop-down menu[/COLOR][/I]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]If[/COLOR] LicLocS.Value = [COLOR=brown]"All"[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    LicLoc.EntireRow.Hidden = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]Else[/COLOR]
        LicLoc.EntireRow.Hidden = [COLOR=Royalblue]True[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] cell [COLOR=Royalblue]In[/COLOR] LicLoc
            [COLOR=Royalblue]If[/COLOR] cell.Value = LicLocS.Value [COLOR=Royalblue]Then[/COLOR]
                cell.Resize([COLOR=crimson]2[/COLOR]).EntireRow.Hidden = [COLOR=Royalblue]False[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
    
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[J_Raab]

BEAUTIFUL! Works like a charm. Would have never thought to just do a resize of the row selection. Appreciate the help Akuini. Hopefully others will find this in their searches.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.