Hide Rows Macro in Excel
Hide Rows Macro in Excel
(OP)
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") 'last rows of the sheet
c = 17 ' rows to hide
Range("a1").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.
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") 'last rows of the sheet
c = 17 ' rows to hide
Range("a1").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.





RE: Hide Rows Macro in Excel
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
RE: Hide Rows Macro in Excel
Thank you so much, if you were here right now, I would take you out for a free lunch.
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")
c = 17 ' rows to hide
Range("home").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