VBA Macro: hide and unhide series of discontinuous columns based upon cell value

sparlee

New Member
Joined
Aug 20, 2015
Messages
2
I am in need of figuring out how to hide columns based upon a cell value. The cell value is created by a control button/form to which there are 6 possible selections.

So based upon the value entered into the cell by the users selection I need to do the following:

If ax143 = 1 then hide the columns M:AV BUT be sure G:L are not hidden.
If ax143 =2 then hide columns G:L and T:AV but be sure N:S are not hidden
If ax143 = 3 then hide columns G:T and AA:AV but be sure U:Z are not hidden
If ax143 =4 then hide columns G:AA and AH:AV but be sure AB:AG are not hidden
If ax143 =5 then hide columns G:AH and AO:AV but not AI:AN
If ax143 =6 then hide columns G:AO but notAP:AV

I have tried doing this every way I can think of and I've not been able to stumble on the correct solution so I'm begging someone to please put me our of my misery and tell me how to do it.

I have nearly zero skills in this area, this is the first time I've ever tried to do this in Excel, and so far I'm at a complete loss.

Thanks for help
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You can manage the logic a couple different ways but this code should get you started:

Code:
Sub HideUnhideCols()

    Dim oWks As Worksheet

    Set oWks = Application.ActiveSheet
    
    If oWks.Range("B2") = 1 Then
        oWks.Range("M:AV").Columns.EntireColumn.Hidden = True
        oWks.Range("G:L").Columns.EntireColumn.Hidden = False
    ElseIf oWks.Range("B2") = 2 Then
        oWks.Range("G:L", "T:AV").Columns.EntireColumn.Hidden = True
        oWks.Range("N:S").Columns.EntireColumn.Hidden = False
    End If
    
    Set oWks = Nothing

End Sub
 
Upvote 0
Taking from your instructions I did it as follows and it does work!! :) - do you see anything I could do in order to improve it?

Sub OptionButton4_Click()


Dim oWks As Worksheet


Set oWks = Application.ActiveSheet

If oWks.Range("AX143") = 1 Then
oWks.Range("M:AV").Columns.EntireColumn.Hidden = True
oWks.Range("G:L").Columns.EntireColumn.Hidden = False

ElseIf oWks.Range("AX143") = 2 Then
oWks.Range("G:L", "T:AV").Columns.EntireColumn.Hidden = True
oWks.Range("N:S").Columns.EntireColumn.Hidden = False
End If

If oWks.Range("AX143") = 3 Then
oWks.Range("G:T", "AA:AV").Columns.EntireColumn.Hidden = True
oWks.Range("U:Z").Columns.EntireColumn.Hidden = False

ElseIf oWks.Range("AX143") = 4 Then
oWks.Range("G:AA", "AH:AV").Columns.EntireColumn.Hidden = True
oWks.Range("AB:AG").Columns.EntireColumn.Hidden = False
End If

If oWks.Range("AX143") = 5 Then
oWks.Range("G:AH", "AO:AV").Columns.EntireColumn.Hidden = True
oWks.Range("AI:AN").Columns.EntireColumn.Hidden = False

ElseIf oWks.Range("AX143") = 6 Then
oWks.Range("G:AO").Columns.EntireColumn.Hidden = True
oWks.Range("AP:AV").Columns.EntireColumn.Hidden = False
End If

Set oWks = Nothing


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top