Help! How to hide/unhide AND hide/unhide rows with one Macro Button?

[thp510]

Hi Everyone. I'm completely new to VBAs but I'm trying to combine a button to do two things.

I have several worksheets (Main, DataSheet1, DataSheet2, DataSheet3, etc.)...
I also have several rows on different worksheets (rows 16-28 on ExcelSheet1, rows 10-15 on ExcelSheet2, rows 9-13 on ExcelSheet3, etc)...

I just want a simple button on the Main worksheet that whenever I push it, it will automatically hide specific rows on one set of worksheets and hide other worksheets all together (EXAMPLE: I click on the button and it hides just rows 16-28 on ExcelSheet1 and also the entire worksheet called DataSheet2; then when I click on the button again, all of that re-appears).

Any have any suggestion or advice?

Thanks!

 

[Fishboy]

Hi Everyone. I'm completely new to VBAs but I'm trying to combine a button to do two things.

I have several worksheets (Main, DataSheet1, DataSheet2, DataSheet3, etc.)...
I also have several rows on different worksheets (rows 16-28 on ExcelSheet1, rows 10-15 on ExcelSheet2, rows 9-13 on ExcelSheet3, etc)...

I just want a simple button on the Main worksheet that whenever I push it, it will automatically hide specific rows on one set of worksheets and hide other worksheets all together (EXAMPLE: I click on the button and it hides just rows 16-28 on ExcelSheet1 and also the entire worksheet called DataSheet2; then when I click on the button again, all of that re-appears).

Any have any suggestion or advice?

Thanks!

Hi thp510, welcome to the boards.

I think you can tailor the following code to suit your needs:

Sub Hide_Toggle()
' This line hides / unhides rows 16 to 28 of ExcelSheet1
Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
' This line hides / unhides all of DataSheet2
Worksheets("DataSheet2").Visible = Not Worksheets("DataSheet2").Visible
End Sub

The parts in bold red can be amended to reflect the sheets / rows you want to hide or unhide. You can also have multiple lines if you have a number of ranges you want to apply this to.

 

[thp510]

Hi thp510, welcome to the boards.

I think you can tailor the following code to suit your needs:

Sub Hide_Toggle()
' This line hides / unhides rows 16 to 28 of ExcelSheet1
Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
' This line hides / unhides all of DataSheet2
Worksheets("DataSheet2").Visible = Not Worksheets("DataSheet2").Visible
End Sub

The parts in bold red can be amended to reflect the sheets / rows you want to hide or unhide. You can also have multiple lines if you have a number of ranges you want to apply this to.

Thanks! However, it looks like I'm getting a 'Run-time error '9': Subscript out of range' I have no idea what that means. Here is the VBA code I used below. Any advice?

Sub Hide_Show_Data()


'


' This line hides / unhides rows 16 to 28 of ExcelSheet1
Sheets("Brian").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Catherine").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Elaine").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Ian").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Jenny_Anne").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)


' This line hides / unhides all of DataSheet2
Worksheets("DataWksht1").Visible = Not Worksheets("DataWksht1").Visible
Worksheets("DataWksht2").Visible = Not Worksheets("DataWksht2").Visible
Worksheets("DataWksht3").Visible = Not Worksheets("DataWksht3").Visible
Worksheets("DataWksht4").Visible = Not Worksheets("DataWksht4").Visible
Worksheets("DataWksht5").Visible = Not Worksheets("DataWksht5").Visible
Worksheets("DataWksht6").Visible = Not Worksheets("DataWksht6").Visible


End Sub

 

[thp510]

AH, nevermind. I see my error--I'm not renaming the code with the actual sheets in the first few lines. Fishboy, you're amazing!!! Thank you so much!

 

[Fishboy]

Hi again thp510. The bold red parts need to be changed to match the range mentioned at the start of each line. Brian, Catherine, Elaine and so on. The error message "Subscript out of range" meant you didn't have a sheet called ExcelSheet1 and Excel didn't like it.

Sub Hide_Show_Data()

' This line hides / unhides rows 16 to 28 of the named sheet
Sheets("Brian").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Catherine").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Elaine").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Ian").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)
Sheets("Jenny_Anne").Range("16:28").EntireRow.Hidden = Not (Sheets("ExcelSheet1").Range("16:28").EntireRow.Hidden)

' This line hides / unhides the named sheets
Worksheets("DataWksht1").Visible = Not Worksheets("DataWksht1").Visible
Worksheets("DataWksht2").Visible = Not Worksheets("DataWksht2").Visible
Worksheets("DataWksht3").Visible = Not Worksheets("DataWksht3").Visible
Worksheets("DataWksht4").Visible = Not Worksheets("DataWksht4").Visible
Worksheets("DataWksht5").Visible = Not Worksheets("DataWksht5").Visible
Worksheets("DataWksht6").Visible = Not Worksheets("DataWksht6").Visible

End Sub

 

[Fishboy]

AH, nevermind. I see my error--I'm not renaming the code with the actual sheets in the first few lines. Fishboy, you're amazing!!! Thank you so much!

Happy to help mate