selecting multiple rows -vba

[lezawang]

Hi
the code below is to type blah in cell a43 then hide rows 25 to 70 for sheets 1 to 5. it works only for sheet1 and then i get error message "selection method of range class failed"

Sub fivesheets()
For i = 1 To 5
    ThisWorkbook.Worksheets(i).Range("a43").Value = "blah"
    ThisWorkbook.Worksheets(i).Rows("25:70").Select
    Selection.EntireRow.Hidden = True
Next i
End Sub

 

[Fluff]

You need to do it like

Sub fivesheets()
For i = 1 To 5
    ThisWorkbook.Worksheets(i).Range("a43").Value = "blah"
    ThisWorkbook.Worksheets(i).Rows("25:70").Hidden = True
Next i
End Sub

You were trying to select cells on a sheet that wasn't active, which can't be done.

 

[lezawang]

I see why did not work now. Thanks a lot for your help.

 

[Fluff]

You're welcome & thanks for the feedback

 

[lezawang]

Sorry I am thinking about your comment: "You were trying to select cells on a sheet that wasn't active, which can't be done.".
The first line in the loop

ThisWorkbook.Worksheets(i).Range("a43").Value = "blah"

that makes worksheets(i) get selected

then the second line

ThisWorkbook.Worksheets(i).Rows("25:70").Select

will not change anything in term which sheet is selected, it is still worksheet(i). This line will select rows 25 to 70 of the current worksheets(i)

then the last line

 Selection.EntireRow.Hidden = True

will hide the selected rows.

Am I right? Thank you.

 

[Fluff]

Am I right?

Nope.
You first point is wrong, all that line of code is doing is writing the value "blah" to cell A43 of a sheet with index number i.
It is not selecting or activating anything.

 

[lezawang]

Got it. Thank you once again.

 

[Fluff]

You're welcome