Macro to show/hide rows

[petejb]

I have a simple macro to hide rows, below
what i need is a macro that will hide/unhide a set number of rows below the row where the macro is acivated. i have a list of names approx 100, each with 11 rows of data below them, i want the data below each name to be hidden until the macro is activated.
Sub ShowUnits()
'
' ShowUnits Macro
' Macro recorded 06/02/2009 by pete
'
'
Rows("5:16").Select
Selection.EntireRow.Hidden = False
End Sub

 

[jasonb75]

That's a valid point, shout if you need any more help with this.

 

[petejb]

thanks jason, just tried your hide below me macro and it worked fine.
thanks again

 

[jasonb75]

Hi Pete, re: the message I sent yesterday, this code has the auto hide function removed so should do exactly what you're looking for (alternate clicks hide and unhide the next range), but with the open/close linked to the cell with the name instead of a button.

Paste this code into a standard module and run after selecting the first name cell on the worksheet.

Sub linkcells()
    Application.ScreenUpdating = False
ar = ActiveCell.Row
ac = ActiveCell.Column
lr = Cells(Rows.Count, ac).End(xlUp).Row
For a = ar To lr Step 12
Cells(a, ac).Select
       ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        ActiveCell.Address, TextToDisplay:=ActiveCell.Text
Next a
   ActiveWorkbook.Save
End Sub

Paste this into worksheet code module for the sheet with the ranges to show / hide

Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
   If Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = False Then
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = True
   ElseIf Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = True Then
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = False
  End If
End Sub

 

[Smitty]

Smitty, have another read of the OP, not sure if I read it right but it looked to me as if he wanted 11 rows below the activecell.

My apologies for not getting back earlier, I had posted as I was leaving the office yesterday, but the post didn't go through.

I was interpreting the OP as needing the ability to hide the 11 rows en masse.

My bad and thanks for taking care of it.

 

[jasonb75]

Hey no prob Smitty, makes a change for me to get something right lol, I looked at it with the same interpretation as an alternative, which the code in my thread here would have taken care of if you're interested http://www.mrexcel.com/forum/showpost.php?p=1832377&postcount=4

Something I started before christmas, and this thread inspired me to finish it

 

[Smitty]

Hey no prob Smitty, makes a change for me to get something right lol, I looked at it with the same interpretation as an alternative, which the code in my thread here would have taken care of if you're interested http://www.mrexcel.com/forum/showpost.php?p=1832377&postcount=4

Something I started before christmas, and this thread inspired me to finish it

Interesting. Too bad I'm swamped on several jobs at the moment, or I'd challenge to you eliminate the selecting.

 

[jasonb75]

Hey no fair I'm only a beginner lol.

 

[Smitty]

Hey no fair I'm only a beginner lol.

No worries, I still am.