Looping rather than code for each row

[Martin]

Hello,

I am new to Excel programming and wondered if there is a more efficient way
of writing some code. Here is my existing code:

If Range("B7").Value = Region Then Rows("7:7").Hidden = False Else
Rows("7:7").Hidden = True
If Range("B8").Value = Region Then Rows("8:8").Hidden = False Else
Rows("8:8").Hidden = True
If Range("B9").Value = Region Then Rows("9:9").Hidden = False Else
Rows("9:9").Hidden = True
If Range("B10").Value = Region Then Rows("10:10").Hidden = False Else
Rows("10:10").Hidden = True
If Range("B11").Value = Region Then Rows("11:11").Hidden = False Else
Rows("11:11").Hidden = True
If Range("B12").Value = Region Then Rows("12:12").Hidden = False Else
Rows("12:12").Hidden = True
If Range("B13").Value = Region Then Rows("13:13").Hidden = False Else
Rows("13:13").Hidden = True
If Range("B14").Value = Region Then Rows("14:14").Hidden = False Else
Rows("14:14").Hidden = True
If Range("B15").Value = Region Then Rows("15:15").Hidden = False Else
Rows("15:15").Hidden = True
If Range("B16").Value = Region Then Rows("16:16").Hidden = False Else
Rows("16:16").Hidden = True
If Range("B17").Value = Region Then Rows("17:17").Hidden = False Else
Rows("17:17").Hidden = True

Rather than copy this for each row is there a way to say something like
start at row 7 and loop until row 17.

The spreadsheet has many headers so I will need to replicate this from rows
20 to 30, rows 28 to 35 etc etc rather than all rows.

Any advice would be greatly appreciated.

Martin

 

[Martin]

Nigel, thank you. You have save me so much time.

Martin

 

[Bob Phillips]

It can be written simpler

Dim lRow As Long

For lRow = 7 To 17

Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region)
Next lRow

 

[Charlie]

and simpler still

For lRow = 7 To 17
Rows(lRow).Hidden = Cells(lRow, 2).Value = Region
Next lRow

 

[Bob Phillips]

How is that simpler?

 

[Charlie]

Two fewer parentheses! (Hey, simpler is simpler.)

 

[Bob Phillips]

No, that is shorter, simpler it is snot.

 

[Charlie]

Argue all you want. Shorter = Simpler. Simpler to type and, for some of us,
simpler to understand without all those extra, redundant parentheses
cluttering up the place.

Some programmers like the extra parentheses (and the extra work required to
type them in) and some don't. New programmers may want to know that
parentheses are not always required.

 

[Bob Phillips]

So

For lRow = 7 To 17
Rows(lRow).Hidden = Cells(lRow, 2).Value = Region OR Cells(lRow, 3).Value =
Country And Not Cells(lRow, 4).Value = Continent
Next lRow

is simple is it?

 

[Charlie]

Exactly!

So

For lRow = 7 To 17
Rows(lRow).Hidden = Cells(lRow, 2).Value = Region OR Cells(lRow, 3).Value =
Country And Not Cells(lRow, 4).Value = Continent
Next lRow

is simple is it?

--
__________________________________
HTH

Bob

 

[Charlie]

although I would make it simpler

.... And Cells(lRow, 4).Value <> Continent

 

[Bob Phillips]

I think that statement sums it up. Goodbye!