Share via

Excel Macro Hiding Columns based on Cell Value

Anonymous
2024-08-12T15:59:58+00:00

I am trying to create a Macro to hide unnecessary Columns. I already created a Macro to hide unnecessary Rows which is functioning normally. I attempted to use similar coding for the Columns as I used for the Rows, but I am not getting the desired results.

My picture of the Worksheet would not upload.

Due to other data I always want to show Columns A to M. I want to use the Value in H2 (Weeks - merged cell with I2) to determine if Columns N to AE should be hidden. I am getting the desired result of all Columns showing when H2 is 0 and 30+. When Weeks is 1 to 29 Columns A to AE are hidden - not desired. When Weeks is 1 to 12 Columns N to AE should be hidden, Weeks is 13 Columns O to AE should be hidden, Weeks is 14 Columns P to AE should be hidden, etc.

Here is the code I created. I know it is not efficient, but I could not figure out how to select Rows or Columns using a Variable so I used numerous Else Ifs. My first goal is to get this functional then I am happy cleaning up the code.

Sub HideColumns()

Dim Weeks As Integer

'Dim HiddenColumns As Range

Weeks = Range("$H$2").Value

'HiddenColumns = Weeks + 10

Columns("A:AE").Select

Selection.EntireColumn.Hidden = False

If Weeks > 0 Then

' Columns("HiddenColumns:AE").Select

' If ((Weeks > 0) And (Weeks <= 12)) Then 

If Weeks = 12 Then 

    'Columns("N:AE").Select 

    Columns("N:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 13 Then 

    Columns("O:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 14 Then 

    Columns("P:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 15 Then 

    Columns("Q:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 16 Then 

    Columns("R:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 17 Then 

    Columns("S:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 18 Then 

    Columns("T:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 19 Then 

    Columns("U:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 20 Then 

    Columns("V:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 21 Then 

    Columns("W:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 22 Then 

    Columns("X:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 23 Then 

    Columns("Y:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 24 Then 

    Columns("Z:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 25 Then 

    Columns("AA:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 26 Then 

    Columns("AB:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 27 Then 

    Columns("AC:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 28 Then 

    Columns("AD:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 29 Then 

    Columns("AE:AE").Select 

    Selection.EntireColumn.Hidden = True 

ElseIf Weeks = 30 Then 

    Columns("N:AE").Select 

    Selection.EntireColumn.Hidden = False 

Else 

    Columns("A:AE").Select 

    Selection.EntireColumn.Hidden = False 

End If

End If

End Sub

I am using Excel Home and Student 2019. These are the first Macros I have tried to create. I used VB many years ago so I am familiar with some of the language, but the VBA references have been difficult to follow.

I wanted to go Line by Line through the code to get the Values being returned by each command to help determine what is wrong, but I wasn't able to get the Help Topics instructions to match up with my version of Excel. Help with Excel is not working. I was only able to use the Microsoft Community.

Any help is very much appreciated. Thank you.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points
    2024-08-13T19:09:01+00:00

    One option would be to run code when the Weekly sheet is activated. In the worksheet module of Weekly:

    Private Sub Worksheet_Activate()
    Dim Weeks As Integer
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Weeks = Val(Range("H2").Value)
    Columns("N:AE").EntireColumn.Hidden = False
    If Weeks >= 12 And Weeks <= 29 Then
        Range(Cells(1, Weeks + 2), Cells(1, 31)).EntireColumn.Hidden = True
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points
    2024-08-12T19:51:21+00:00

    Try this version.

    Sub HideColumns()
    Dim Weeks As Integer
    Application.ScreenUpdating = False
    Weeks = Val(Range("H2").Value)
    Columns("N:AE").EntireColumn.Hidden = False
    If Weeks >= 12 And Weeks <= 29 Then
        Range(Cells(1, Weeks + 2), Cells(1, 31)).EntireColumn.Hidden = True
    End If
    Application.ScreenUpdating = True
End Sub

    Keep in mind that you have to run this macro yourself. If you want to hide/unhide columns automatically if you edit cell H2:

    • Right-click the sheet tab.
    • Select 'View Code' from the context menu,
    • Copy the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim Weeks As Integer If Not Intersect(Range("H2"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Weeks = Val(Range("H2").Value) Columns("N:AE").EntireColumn.Hidden = False If Weeks >= 12 And Weeks <= 29 Then Range(Cells(1, Weeks + 2), Cells(1, 31)).EntireColumn.Hidden = True End If Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2024-08-13T13:19:46+00:00

    It works for me. Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-08-13T12:07:22+00:00

    I studied your code. It helped me make my If / Else If statements work to hide the Columns. I changed:

    Columns("XX:XX").Select

    Selection.EntireColumn.Hidden = True

    to

    Columns("XX:XX").EntireColumn.Hidden = True

    I was not able to get your code to work. It seems that this line is not hiding the columns and needs to be modified:

    Range(Cells(1, Weeks + 2), Cells(1, 31)).EntireColumn.Hidden = True

    Thank you for all of your help!!!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-08-12T20:02:43+00:00

    Thank you so much.

    I will study this to make sure I understand the calls and the processes. I will reach out if I have any questions.

    Was this answer helpful?

    0 comments