×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Hide Rows Macro in Excel

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.

RE: Hide Rows Macro in Excel

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

RE: Hide Rows Macro in Excel

(OP)
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")
    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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources