Nov 25 2020 05:18 AM
Hi, I have a spreadsheet that uses VBA to hide rows upon opening. I would like the workbook to open on the first tab. I have seen the formula to use for this:
Private Sub Workbook_Open()
Worksheets("General Instructions").Activate
Sheets("Americas").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:49").EntireRow.Hidden = True
Sheets("ANZASAF").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:24").EntireRow.Hidden = True
Rows("27:36").EntireRow.Hidden = True
Rows("39:45").EntireRow.Hidden = True
Sheets("CEU").Activate
Rows("9:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:50").EntireRow.Hidden = True
Sheets("GB").Activate
Rows("8:16").EntireRow.Hidden = True
Rows("19:27").EntireRow.Hidden = True
Rows("30:39").EntireRow.Hidden = True
Rows("42:47").EntireRow.Hidden = True
End Sub
Nov 25 2020 05:40 AM
Private Sub Workbook_Open()
Worksheets("General Instructions").Activate
With Sheets("Americas")
.Rows("8:18").EntireRow.Hidden = True
.Rows("21:30").EntireRow.Hidden = True
.Rows("33:42").EntireRow.Hidden = True
.Rows("45:49").EntireRow.Hidden = True
End With
With Sheets("ANZASAF")
.Rows("8:18").EntireRow.Hidden = True
.Rows("21:24").EntireRow.Hidden = True
.Rows("27:36").EntireRow.Hidden = True
.Rows("39:45").EntireRow.Hidden = True
End With
With Sheets("CEU")
.Rows("9:18").EntireRow.Hidden = True
.Rows("21:30").EntireRow.Hidden = True
.Rows("33:42").EntireRow.Hidden = True
.Rows("45:50").EntireRow.Hidden = True
End With
With Sheets("GB")
.Rows("8:16").EntireRow.Hidden = True
.Rows("19:27").EntireRow.Hidden = True
.Rows("30:39").EntireRow.Hidden = True
.Rows("42:47").EntireRow.Hidden = True
End With
End Sub
Nov 25 2020 06:00 AM
Thank you for your response i have added the End With that I was missing and I get this error message when I open the file now
Nov 25 2020 06:22 AM - edited Nov 25 2020 06:25 AM
@North_Yorks
I'm not sure I understand what you mean by " added the End With that I was missing". Perhaps you are missing that instead of
Sheets("Americas").Activate
I'm using
With Sheets("Americas")
and where you have something like
Rows("8:18").EntireRow.Hidden = True
I have added a period to the front so it is now:
.Rows("8:18").EntireRow.Hidden = True
I'm using an entirely different approach that happens to look very similar. You are trying to activate the sheet and hoping that the rows will reference the active sheet. I'm explicitly telling Excel to link the rows to the specific worksheet and I'm not activating each sheet to do it. By not activating each worksheet, your first activate remains the active worksheet.
Without the WITH statement the new code would look like this (which may make things clearer),
Sheets("Americas").Rows("8:18").EntireRow.Hidden = True
Sheets("Americas").Rows("21:30").EntireRow.Hidden = True
Sheets("Americas").Rows("33:42").EntireRow.Hidden = True
Sheets("Americas").Rows("45:49").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("8:18").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("21:24").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("27:36").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("39:45").EntireRow.Hidden = True
The WITH statement allows us to not duplicate the Sheets("sheet name") on every row.
Nov 25 2020 06:25 AM
@Craig Hatmaker apologies I see my formula was missing more than I thought.
I have now copied your original instructions and it has worked. Many thanks
Nov 25 2020 08:28 AM
If you want to make the same changes on multiple sheets, a more succinct version might be
Option Explicit
Private Sub Workbook_Open()
HideRows
End Sub
Sub HideRows()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "GeneralInstructions" Then
ws.Rows("8:18").Hidden = True
ws.Rows("21:30").Hidden = True
End If
Next
Sheets("GeneralInstructions").Select
End Sub
It is the final (or in this case the only) Select that determines what you see when control is returned from the macro.
[I didn't bother about EntireRow because the Row object is already the entire row]
Sub UnhideRows()
Dim ws As Worksheet
For Each ws In Sheets
ws.Rows.Hidden = False
Next
End Sub
Hi @Craig Hatmaker Good to see you around! Do not hesitate to improve my programming style, It has a long way to go!