Forum Discussion
North_Yorks
Nov 25, 2020Copper Contributor
VBA Formula
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() Workshe...
PeterBartholomew1
Nov 25, 2020Silver Contributor
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 SubIt 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!