Hide and Unhide Multiple Rows

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
149
VBA Code:
Option Explicit

Private bToggleMINUS As Boolean
Private bTogglePLUS As Boolean

Public Sub Btn_MINUS()
If Not bToggleMINUS Then
Me.Rows("26:53").Hidden = True
Me.Rows("82:107").Hidden = True
Me.Rows("137:161").Hidden = True
Me.Rows("191:215").Hidden = True
Me.Rows("244:269").Hidden = True
Me.Rows("299:323").Hidden = True
End If
bToggleMINUS = Not bToggleMINUS
End Sub

Public Sub Btn_PLUS()
If Not bTogglePLUS Then
Me.Rows("26:53").Hidden = False
Me.Rows("82:107").Hidden = False
Me.Rows("137:161").Hidden = False
Me.Rows("191:215").Hidden = False
Me.Rows("244:269").Hidden = False
Me.Rows("299:323").Hidden = False
End If
bTogglePLUS = Not bTogglePLUS
End Sub

I am using the above code to hide multiple rows on a worksheet. The problem is that I have to sometimes click the buttons twice to make it work. I also would like a real toggle button that changes text so I can do it with just one button. I have 12 sheets in one workbook so I will add this button to each worksheet. Any help would be appreciated.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
What sort of "button" are you using to run the code?
 

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
149
What sort of "button" are you using to run the code?
If there is someplace to put the code only once and just add a button to each sheet, that would be a much better solution, that way I wouldn't have to add the code to each worksheet...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
If you put this code in a standard module, you can then assign it to each button
VBA Code:
Sub RandyD()
   With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
      Rows("26:53").Hidden = .Text = "Hide"
      Rows("82:107").Hidden = .Text = "Hide"
      .Text = IIf(.Text = "Hide", "Unhide", "Hide")
   End With
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

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
Top