Convert Repetitive Excel to Simpler Case Statement

[belang46]

Hi everyone,

I have the below code in a spreadsheet five times changing only the range...I can't figure out the syntax to make this a "Select Case" type statement. I'm still a novice VBA'er...could anyone please provide me some guidance into how i need to structure this so i can clean the code up a bit?

I colored the only piece that changes in red...I searched the internet for an hour and couldn't figure out exactly how to change this up to make it a bit simpler/cleaner looking.

Any guidance or push in the right direction is appreciated!!! THANKS!
-belang46

    For Each cel In Sheets(strOrderSheet).Range([COLOR=red]strUnitsRange[/COLOR]).Cells
        If cel.Value = 0 Then cel.EntireRow.Hidden = True
        If cel.Value <> 0 Then cel.EntireRow.Hidden = False
    Next cel

 

[shg]

How about ...

    For Each cel In Sheets(strOrderSheet).Range(strUnitsRange).Cells
        cel.EntireRow.Hidden = cel.Value = 0 
    Next cel

 

[belang46]

Hi-Thanks for the quick response.

I'm actually trying to find a way to pass a different range through only one formula 5x...i was thinking some sort of Case Statement.

The variables are
strUnitsRange
strUnitsRange1
strUnitsRange2
strUnitsRange3
strUnitsRange4
strUnitsRange5.

I basically have the exact "For" statement I posted in the sheet 5x, each time with a different variable (listed above)...but I feel like there should be a "cleaner" way to put it into VBA. I figured a Case would work, but I can't figure out how to put it into VBA. Does this help clarify my question at all? Please let me know...thanks for the quick response!!!

 

[shg]

Ah.

    For Each v In Array(strUnitsRange, strUnitsRange1, _
                        strUnitsRange2, strUnitsRange3, _
                        strUnitsRange4, strUnitsRange5)
        For Each cel In Worksheets(strOrderSheet).Range(v).Cells
            cel.EntireRow.Hidden = cel.Value = 0
        Next cel
    Next v

 

[belang46]

Thank you...this is exactly what I was looking for!

Regards,
belang46

 

[shg]

You're welcome, glad it helped.

 

[belang46]

My apologies...one last thing...how do I set this up in the sheet...i keep getting the error because I haven't defined V.

Dim V as Integer/Range? I set up the "strOrder" names as Strings...it keeps telling me I need an "Object or Variable"

Sorry ...but Thanks again.

 

[shg]

Dim v As Variant