Hide Columns With Dynamic Range

[The Doctor]

I have a worksheet that when I update the date the number of the month's position in the fiscal year populates in A4 (numbers 1-12). I want to use the number in A4 to determine the range of cells to hide. I've a helper row (row 4) that will contain the same numbers (1-12). I'd like the code to hide column b through the column before the matching month. I've attached a sample book to clarify what I'm looking to do.

 

[eibi]

Dr.,

The only way I know to do this is with VBA. See attached; I think the button will do what you want...

Sub HideColumns()

Dim TargetMonth As Integer
    TargetMonth = Sheets(1).Range("$A$4").Value

Dim TargetColumn As Double
    TargetColumn = Application.WorksheetFunction.Match(TargetMonth, Sheets(1).Range("$B$4:$S$4"), 0)

Sheets(1).Columns("B:S").EntireColumn.Hidden = False
Sheets(1).Columns("B:" & Chr(64 + TargetColumn)).EntireColumn.Hidden = True

End Sub

 

[The Doctor]

Looks like it works fine. Can you break down how the code works?

 

[eibi]

sure.

Line 1-2: Establish a variable (called TargetMonth) and assign a value to it from Cell A4

Line 3-4: Establish a variable (called TargetColumn) to define the final column in the range that will be hidden, and assign it a numerical value using a Match() function to find the position of the TargetMonth in the range B4:S4.

Line 5: Unhide all columns previously hidden.

Line 6: Hide all columns from Column B thru the Target Column, converted from a numerical value assigned in Line 3 above, to an alpha value using chr(64+TargetColumn)​

If that's helpful enough, just reply or like this post. If you need more specifics, you'll have to explain which part needs special explanation.