Including more target cells into existing code (VBA)

[JBM91]

Hi experts,

So I have this code, which works like a charm for its intended purpose:

Private Sub Worksheet_Change(ByVal Target As Range)

'Sort planning based on week

Dim cel As Range, Headers As Range
Dim s As String

Set Headers = Range("i1:ABJ1")

If Target.Address = "$G$17" Then
s = Target.Value
Application.ScreenUpdating = False
If s = "" Then
Headers.EntireColumn.Hidden = False

Else

For Each cel In Headers
cel.EntireColumn.Hidden = Not cel.Value = s
Next cel
End If
Application.ScreenUpdating = True

End If

End Sub

However, I would like to have a "sub-version" of this that would then be tied to a button. Other than being a sub and not a worksheet change, it'd also have to either include more target addresses, or, alternatively, also target values that are within -2 of the If Target.Address = "$G$17". E.g. if the

If Target.Address = "$G$17"

Is set to 5, it'd have to also target headers with 4 and 3 - if that makes sense.

Any tips, tricks or help would be greatly appreciated.

 

[Norie]

If this code was triggered from a button there would be no Target argument.

What would you want to replace that with?

 

[JBM91]

If this code was triggered from a button there would be no Target argument.

What would you want to replace that with?

I'm open for any ideas and/or recommendations really!
As long as it manages to do what it's supposed to do - any suggestions?

 

[Norie]

What is the code supposed to do?

Is it to hide specific columns based on the value in specific cells?

What other ranges would you want to add to the code?

 

[JBM91]

What is the code supposed to do?

Is it to hide specific columns based on the value in specific cells?

What other ranges would you want to add to the code?

Yeah, but the other way around; it unhides columns in the range I1:ABJ1 that have a header value which corresponds to the value in the target address and hide everything else.
Instead of G17 it would be then be E20, E21 and E22.

 

[JBM91]

Edit.

Confusing phrasing on my part, sorry: It hides all columns in the range I1:ABJ1 that does not have a header value that corresponds to the value in the target address.

 

[Tom Baron]

This should do what you want, Starting will cell G17 and working down you can enter column numbers, these references unhide the relevant columns in the workbook:

Sub test()
Dim cel As Range, Headers As Range, columnarr() As Variant, x As Single, rowc As Double
Dim s As String
Set Headers = Range("i1:ABJ1")
Headers.EntireColumn.Hidden = False
lastr = Range("g1000").End(xlUp).Row
rowc = Range(Range("g17"), "g" & lastr).Rows.Count
' Set number of items in array
ReDim columnarr(1 To rowc)
'Array created storing column numbers
columnarr = Application.Transpose(Range(Range("g17"), Range("g" & lastr)))
Application.ScreenUpdating = False
Headers.EntireColumn.Hidden = True
x = 1
'do while unhides columns based on references in column G
Do While x <= UBound(columnarr)
Headers.Cells(1, columnarr(x)).EntireColumn.Hidden = False
x = x + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[JBM91]

This should do what you want, Starting will cell G17 and working down you can enter column numbers, these references unhide the relevant columns in the workbook:

View attachment 4009

Sub test()
Dim cel As Range, Headers As Range, columnarr() As Variant, x As Single, rowc As Double
Dim s As String
Set Headers = Range("i1:ABJ1")
Headers.EntireColumn.Hidden = False
lastr = Range("g1000").End(xlUp).Row
rowc = Range(Range("g17"), "g" & lastr).Rows.Count
' Set number of items in array
ReDim columnarr(1 To rowc)
'Array created storing column numbers
columnarr = Application.Transpose(Range(Range("g17"), Range("g" & lastr)))
Application.ScreenUpdating = False
Headers.EntireColumn.Hidden = True
x = 1
'do while unhides columns based on references in column G
Do While x <= UBound(columnarr)
Headers.Cells(1, columnarr(x)).EntireColumn.Hidden = False
x = x + 1
Loop
Application.ScreenUpdating = True
End Sub

Hi Tom,

Thanks for the suggestion! I might be wrong, but won't this formula assume that column G is exclusively used for the purpose of cell reference for columns to hide?
In my case, everything below G22 is populated with data. Although I like the idea of having a dynamic formula that'll update when you enter a new value in the G-column, what I need is probably a "static" or fixed reference to cells G20, G21 and G22 only.

Is it possible to alter the formula you provided to do that instead?

 

[JBM91]

This should do what you want, Starting will cell G17 and working down you can enter column numbers, these references unhide the relevant columns in the workbook:

View attachment 4009

Sub test()
Dim cel As Range, Headers As Range, columnarr() As Variant, x As Single, rowc As Double
Dim s As String
Set Headers = Range("i1:ABJ1")
Headers.EntireColumn.Hidden = False
lastr = Range("g1000").End(xlUp).Row
rowc = Range(Range("g17"), "g" & lastr).Rows.Count
' Set number of items in array
ReDim columnarr(1 To rowc)
'Array created storing column numbers
columnarr = Application.Transpose(Range(Range("g17"), Range("g" & lastr)))
Application.ScreenUpdating = False
Headers.EntireColumn.Hidden = True
x = 1
'do while unhides columns based on references in column G
Do While x <= UBound(columnarr)
Headers.Cells(1, columnarr(x)).EntireColumn.Hidden = False
x = x + 1
Loop
Application.ScreenUpdating = True
End Sub

Hi Tom,

So I tried modifying the formula myself according to the comments I made in my previous reply, please see below:

Sub test()
Dim cel As Range, Headers As Range, columnarr() As Variant, x As Single, rowc As Double
Dim s As String

Set Headers = Range("I1:ABJ1")

Headers.EntireColumn.Hidden = False
lastr = Range("G22").Row
rowc = Range(Range("G20"), "G" & lastr).Rows.Count

' Set number of items in array
ReDim columnarr(1 To rowc)
'Array created storing column numbers
columnarr = Application.Transpose(Range(Range("G20"), Range("G" & lastr)))
Application.ScreenUpdating = False
Headers.EntireColumn.Hidden = True
    x = 1

    'do while unhides columns based on references in column G
Do While x <= UBound(columnarr)
Headers.Cells(1, columnarr(x)).EntireColumn.Hidden = False
x = x + 1

Loop
Application.ScreenUpdating = True

End Sub

However, when I run it, it seems as if the formula will stop at the K-column and just hide everything past that. When I enter the developer tab and try to run the code in steps, it would appear that the reason is that the macro will only run this part:

    'do while unhides columns based on references in column G
Do While x <= UBound(columnarr)
Headers.Cells(1, columnarr(x)).EntireColumn.Hidden = False
x = x + 1

Loop

3 times before ending the sub, thereby only "evaluating" the 3 initial columns I, J and K - is there a workaround to that?