VBA Code to Unhide All Columns When User Clicks on a Worksheet
Hello Everyone,
I am a basic Excel user attempting my first VBA macro and being reminded that simple <> easy (at least not for me).
Desired VBA outcome – When a user clicks on the “Data” worksheet tab, I want the macro to unhide any hidden columns, display columns A – ZZ, and only display rows 1-18. I used ChatGPT to start. Countless iterations later, this ChatGPT code displays the rows correctly but does not unhide the hidden columns and show all columns.
=========================ChatGPT=========================
Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim col As Range
‘ Set the worksheet object to the “Data” worksheet
Set ws = ThisWorkbook.Sheets(“Data”)
‘ Hide all rows and columns
ws.Rows.Hidden = True
ws.Columns.Hidden = True
‘ Set the range for rows 1 to 18 and columns A to ZZ
Set rowRange = ws.Rows(“1:18”)
Set colRange = ws.Columns(“A:ZZ”)
‘ Unhide rows and columns
rowRange.Hidden = False
colRange.Hidden = False
End Sub
=========================================================
Any suggestions to fix this are greatly appreciated!
Thom
Hello Everyone,I am a basic Excel user attempting my first VBA macro and being reminded that simple <> easy (at least not for me). Desired VBA outcome – When a user clicks on the “Data” worksheet tab, I want the macro to unhide any hidden columns, display columns A – ZZ, and only display rows 1-18. I used ChatGPT to start. Countless iterations later, this ChatGPT code displays the rows correctly but does not unhide the hidden columns and show all columns. =========================ChatGPT=========================Private Sub Worksheet_Activate()Dim ws As WorksheetDim col As Range’ Set the worksheet object to the “Data” worksheetSet ws = ThisWorkbook.Sheets(“Data”)’ Hide all rows and columnsws.Rows.Hidden = Truews.Columns.Hidden = True’ Set the range for rows 1 to 18 and columns A to ZZSet rowRange = ws.Rows(“1:18”)Set colRange = ws.Columns(“A:ZZ”)’ Unhide rows and columnsrowRange.Hidden = FalsecolRange.Hidden = FalseEnd Sub=========================================================Any suggestions to fix this are greatly appreciated!Thom Read More