Forum Discussion

ThomT25's avatar
ThomT25
Copper Contributor
Apr 23, 2024

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" works...
developer
Excel
  • HansVogelaar's avatar
    HansVogelaar
    Apr 23, 2024

    ThomT25 

    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
HansVogelaar's avatar
HansVogelaar
MVP

ThomT25 

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
ThomT25's avatar
ThomT25
Copper Contributor
Apr 23, 2024
Hello Hans,

THANK you SO much for solving my problem. I mistakenly inserted a module and pasted the code there, but it did not work. I then inserted the code into the worksheet object and it worked perfectly. - Thank you again and enjoy the rest of your week! - Thom

Resources