Hide Rows Macro in Excel

[belea]

Hi,

I'm not very technical (financial analyst), so can anyone help me write a loop for the procedure below?
The procedure below will hide certain rows.
My excel sheet's structure is follow: one division info, 100 rows down is dollar info for that division.
400 rows down, another division, and 100 rows down, dollar info...
So, in a way, the loop should jump down to 100 (hide) and then 400 (hide) and then 100 (hide) and then 400...

Sub nexthide()

Dim c As Long

finalrow = Range("a65536&quot 'last rows of the sheet
c = 17 ' rows to hide
Range("a1&quot.Select 'beginning of the sheet
'Somewhere here is the loop
'Do until finalrow
ActiveCell.Offset(19, 0).EntireRow.Resize(rowsize:= c ).Hidden = True

'keep the counter somehow???
loop

End Sub

I tried the loop for awhile, but not very successful, so if anyone could help. Thanks a bunch.

 

[johnwm]

Try this:

Sub Macro1()
Dim hRows As Long
Const myHidden As Long = 17
Const Jump1 As Long = 100
Const Jump2 As Long = 400
Const LastRow As Long = 65535
For hRows = 1 To LastRow Step Jump1 + Jump2
Rows(CStr(hRows + Jump1) & ":" & CStr(hRows + myHidden + Jump1)).Select
Selection.EntireRow.Hidden = True
Rows(CStr(hRows + Jump1 + Jump2) & ":" & CStr(hRows + Jump1 + Jump2 + myHidden)).Select
Selection.EntireRow.Hidden = True
Next hRows
End Sub

Just open the VB Editor and stick this in. You will need to change the 4 constants to suit your needs. This is not completed production code, it has no error checking, but it may start you in the right direction.

Note that each of the lines that start with 'Rows' and end with '.Select' is on one line not split

Let me know if this helps

 

[belea]

John,

Thank you so much, if you were here right now, I would take you out for a free lunch. ) You have no idea how helpful this is.

I had to play around a little to figure out exactly where I wanted it to hide. But your code was all I need.

You know something funny, I did this Sub below, it does exactly what I wanted, but I think it doesn't recognize finalrow, and so, it just loop through the end of the worksheet, and then I got a error message "application-defined or object defined error". And I didn't know how to fix it. But who cares, your code make way more sense and more logical than mine and error-free.

Sub nexthide()
Dim c As Long
finalrow = Range("A3743&quot
c = 17 ' rows to hide
Range("home&quot.Select 'home as a1
Do
ActiveCell.Offset(19, 0).EntireRow.Resize(rowsize:=c).Hidden = True

ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select

Loop Until finalrow
End Sub

Thanks again,
Lea