| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping rather than code for each row
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
It can be written simpler
Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
and simpler still
For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
How is that simpler?
-- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
Two fewer parentheses! (Hey, simpler is simpler.)
"Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
No, that is shorter, simpler it is snot.
-- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
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" wrote: No, that is shorter, simpler it is snot. -- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
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" wrote in message ... 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" wrote: No, that is shorter, simpler it is snot. -- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
Exactly!
"Bob Phillips" wrote: 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" wrote in message ... 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" wrote: No, that is shorter, simpler it is snot. -- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
although I would make it simpler
.... And Cells(lRow, 4).Value < Continent "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: No, that is shorter, simpler it is snot. -- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping rather than code for each row
I think that statement sums it up. Goodbye!
-- __________________________________ HTH Bob "Charlie" wrote in message ... Exactly! "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: No, that is shorter, simpler it is snot. -- __________________________________ HTH Bob "Charlie" wrote in message ... Two fewer parentheses! (Hey, simpler is simpler.) "Bob Phillips" wrote: How is that simpler? -- __________________________________ HTH Bob "Charlie" wrote in message ... and simpler still For lRow = 7 To 17 Rows(lRow).Hidden = Cells(lRow, 2).Value = Region Next lRow "Bob Phillips" wrote: It can be written simpler Dim lRow As Long For lRow = 7 To 17 Rows(lRow).Hidden = (Cells(lRow, 2).Value = Region) Next lRow -- __________________________________ HTH Bob "Martin" wrote in message ... Nigel, thank you. You have save me so much time. Martin "Nigel" wrote: Dim lRow As Long For lRow = 7 To 17 If Cells(lRow, 2) = Region Then Rows(lRow).EntireRow.Hidden = True Else Rows(lRow).EntireRow.Hidden = False End If Next lRow -- Regards, Nigel "Martin" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code looping through files | Excel Programming | |||
| Excel Looping code.... | Excel Programming | |||
| Help with Looping Code | Excel Programming | |||
| Looping macros using VB code | Excel Discussion (Misc queries) | |||
| Code looping when it should not | Excel Programming | |||
Linear Mode
