Macro to show/hide rows

P

petejb

New Member
Joined
Feb 6, 2009
Messages
5
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
 
Sort by date Sort by votes
That's a valid point, shout if you need any more help with this.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.

Code: 
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

Code: 
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
 
Upvote 0
jasonb75 said:
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.
Click to expand...

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. :)
 
Upvote 0
jasonb75 said:
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 :)
Click to expand...

Interesting. Too bad I'm swamped on several jobs at the moment, or I'd challenge to you eliminate the selecting. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Button to hide/unhide rows with specific text
2
Replies
11
Views
300
NoSparks
N
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
368
Joe4
Joe4
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
400
Joe4
Joe4
A
  • Question Question
Macro VBA hide/unhide with veryHidden option and VBA password
Replies
1
Views
561
alexbw
A
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
1K
PuntingJawa
PuntingJawa

Forum statistics

Threads
1,222,902
Messages
6,168,938
Members
452,227
Latest member
sam1121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top