VBA Code to Unhide All Columns When User Clicks on a Worksheet

Question

Hello Everyone,I am a basic Excel user attempting my first VBA macro and being reminded that simple &lt;&gt; easy (at least not for me).&nbsp;Desired VBA outcome - When a user clicks on the "Data" worksheet tab, I want the macro to unhide any hidden columns, display columns A - CZ, 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.&nbsp;=========================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 CZSet rowRange = ws.Rows("1:18")Set colRange = ws.Columns("A:CZ")' Unhide rows and columnsrowRange.Hidden = FalsecolRange.Hidden = FalseEnd Sub=========================================================Any suggestions to fix this are greatly appreciated!Thom

hansvogelaar · Accepted Answer

ThomT25&nbsp;
Try this version. And never rely on ChatGPT, please.
Private Sub Worksheet_Activate()
    Dim rowRange As Range
    Dim colRange As Range

' Hide all rows and columns
    Rows.Hidden = True
    Columns.Hidden = True

' Set the range for rows 1 to 18 and columns A to CZ
    Set rowRange = Rows("1:18")
    Set colRange = Columns("A:CZ")

' Unhide rows and columns
    rowRange.EntireRow.Hidden = False
    colRange.EntireColumn.Hidden = False
    Application.Goto Range("A1")
End Sub

Forum Discussion

VBA Code to Unhide All Columns When User Clicks on a Worksheet

Share

Resources