How to hide and unhide columns based off of a numeric cell value using VBA

[tommyboy1019]

Hey guys,

This is my first time posting, but I've definitely used the forums for help before.
I need to expand and collapse columns based off of the numerical value in cell A1. And I need to use VBA to do so.
Columns B:Z will expand and collapse. For ex. A1 = 0 then no columns show. A1=10 then B:K will show. If I go back to A1=0 then no columns will show again. If A1=25 then B:Z will show.

Please help, I have very armature knowledge in VBA. Any assistance will be appreciated.

Thanks!

 

[Yongle]

Welcome to the forum tommyboy1019

Place in sheet module (right-click on sheet tab \ View Code \ paste in code window)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then
        Columns("B:Z").Hidden = True
        Select Case Range("A1")
            Case 0:     Columns("B:Z").Hidden = True
            Case 10:    Columns("B:K").Hidden = False
            Case 25:    Columns("B:Z").Hidden = False
        End Select
    End If
End Sub

Hide all the columns first and unhide the ones you want to see
Case 0 is probably unnecessary, but does no harm, and makes the outcome clear

 

[dmt32]

Hi,
Try following & see if does what you want

Place code in your sheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then
            Me.Columns("B:Z").EntireColumn.Hidden = IIf(Target.Value = 25, False, True)
    If Target.Value = 10 Then Me.Columns("B:K").EntireColumn.Hidden = False
    End If
End Sub

Note that change event will not trigger if the value in A1 is being changed by formula - Calculation event can be used to resolve.

Dave

 

[SamoZain]

Hi Guys, I have similar question and I was hoping someone can help me with it as I am trying to develop my VBA capabilities.

I have an excel file that will hide and unhide based on multiple values which mainly year and month criteria, meaning:
if it is 2016 chosen then all the columns of 2016 will be unhiden. but if within 2016 I want only column of Feb then I will choose only Feb.

my question is how can I make nested choices in a way that if I choose Feb (After choosing 2016) it will show me all the Feb which has all the years in???

Bes

 

[Fluff]

@SamoZain
Please do not "Hijack" another persons thread. You need to start a thread of your own.
Thanks

 

[tommyboy1019]

Thank you Yongle for the warm welcome,

Much obliged for the speedy response. I followed your instructions and pasted the code in. Unfortunately, nothing is happening when I enter my values in cell A1.

 

[tommyboy1019]

dmt32,

Thank you for your response. I can't believe how fast you guys are. Unfortunately, the code is not working. I enter 10 or 25 in cell A1, but none of the columns are unhiding. You can tell something is registering because the outline in A1 does not move to A2 when I hit enter.

 

[Joe4]

It sounds to me like maybe you did not put the VBA code in the correct.
It needs to be in the specific sheet module that you want it to run on - it cannot be in a "General" module.
The surest way to get to the correct module is to go to the sheet you want to apply this to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up.

 

[dmt32]

dmt32,

Thank you for your response. I can't believe how fast you guys are. Unfortunately, the code is not working. I enter 10 or 25 in cell A1, but none of the columns are unhiding. You can tell something is registering because the outline in A1 does not move to A2 when I hit enter.

MrExcel is a very helpful forum

Have you placed the code in your sheets code page as detailed in Yongles post?

Dave

 

[tommyboy1019]

Hello Joe4,

Thanks for replying to my thread, any advice helps. I'm pretty darn sure I've got it in the right sheet. I right clicked the tab and entered the code in its designated sheet.
Is the code working for you by chance? That would be a sure fire way to know that i'm doing something wrong.