VBA solution need to hide columns

[arup1032]

I have an Excel sheet where columns A and B need to be visible always. I have some range

C to O
Q to AC
AE to AQ
AS TO BE
BG TO BS
BU TO CG
CI TO CU
CW TO DI
DK TO DW
DY TO EK
EM TO EY
FA TO FM
FO TO GA
GC TO GO
GQ TO HC
HE TO HQ
HS TO IE
IG TO IS
IU TO JG
JI TO JU

i need When I will select cell A3 then only COLUMN A, B AND C TO O will be visible other COLUMN will be hidden

When I will select cell A4 then only COLUMN A, B AND Q TO AC will be visible other COLUMN will be hidden

When I will select cell A5 then only COLUMN A, B AND AE to AQ will be visible other COLUMN will be hidden

and so on for cell A22

I have attached the excel file link below. You can download it from there.

Excel File

 

[footoo]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col&
If Not Intersect(Target, [A2:A22]) Is Nothing Then
    Select Case Target.Row
        Case 2: col = 3
        Case 3: col = 17
        Case 4: col = 31
        Case 5: col = 45
        Case 6: col = 59
        Case 7: col = 73
        Case 8: col = 87
        Case 9: col = 101
        Case 10: col = 115
        Case 11: col = 129
        Case 12: col = 143
        Case 13: col = 157
        Case 14: col = 171
        Case 15: col = 185
        Case 16: col = 199
        Case 17: col = 213
        Case 18: col = 227
        Case 19: col = 241
        Case 20: col = 255
        Case 21: col = 269
        Case 22: col = 283
    End Select
    Range(Columns(3), Columns(Columns.Count)).EntireColumn.Hidden = True
    Range(Columns(col), Columns(col + 12)).EntireColumn.Hidden = False
Else: Range(Columns(3), Columns(Columns.Count)).EntireColumn.Hidden = False
End If
End Sub

 

[footoo]

Revised :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col&
If Target.Count <> 1 Then Exit Sub
If Not Intersect(Target, [A3:A22]) Is Nothing Then
    Select Case Target.Row
        Case 3: col = 3
        Case 4: col = 17
        Case 5: col = 31
        Case 6: col = 45
        Case 7: col = 59
        Case 8: col = 73
        Case 9: col = 87
        Case 10: col = 101
        Case 11: col = 115
        Case 12: col = 129
        Case 13: col = 143
        Case 14: col = 157
        Case 15: col = 171
        Case 16: col = 185
        Case 17: col = 199
        Case 18: col = 213
        Case 19: col = 227
        Case 20: col = 241
        Case 21: col = 255
        Case 22: col = 269
    End Select
    Range(Columns(3), Columns(Columns.Count)).EntireColumn.Hidden = True
    Range(Columns(col), Columns(col + 12)).EntireColumn.Hidden = False
Else: Range(Columns(3), Columns(Columns.Count)).EntireColumn.Hidden = False
End If
End Sub

 

[Herakles]

I have an Excel sheet where columns A and B need to be visible always. I have some range

C to O
Q to AC
AE to AQ
AS TO BE
BG TO BS
BU TO CG
CI TO CU
CW TO DI
DK TO DW
DY TO EK
EM TO EY
FA TO FM
FO TO GA
GC TO GO
GQ TO HC
HE TO HQ
HS TO IE
IG TO IS
IU TO JG
JI TO JU

i need When I will select cell A3 then only COLUMN A, B AND C TO O will be visible other COLUMN will be hidden

When I will select cell A4 then only COLUMN A, B AND Q TO AC will be visible other COLUMN will be hidden

When I will select cell A5 then only COLUMN A, B AND AE to AQ will be visible other COLUMN will be hidden

and so on for cell A22

I have attached the excel file link below. You can download it from there.

Excel File

Put this code into the worksheet code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrColumns() As String

    arrColumns = Split("C:O,Q:AC,AE:AQ,AS:BE,BG:BS,BU:CG,CI:CU,CW:DI,DK:DW,DY:EK,EM:EY,FA:FM,FO:GA,GC:GO,GQ:HC,HE:HQ,HS:IE,IG:IS,IU:JG,JI:JU", ",")
    
    If Not Intersect(Target, Range("A3:A22")) Is Nothing Then
    
        Range("C1").Resize(1, Cells.Columns.Count - 2).EntireColumn.Hidden = True
        
        Range(arrColumns(Target.Row - 3)).EntireColumn.Hidden = False
    
    End If

End Sub

 

[arup1032]

Put this code into the worksheet code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrColumns() As String

    arrColumns = Split("C:O,Q:AC,AE:AQ,AS:BE,BG:BS,BU:CG,CI:CU,CW:DI,DK:DW,DY:EK,EM:EY,FA:FM,FO:GA,GC:GO,GQ:HC,HE:HQ,HS:IE,IG:IS,IU:JG,JI:JU", ",")
   
    If Not Intersect(Target, Range("A3:A22")) Is Nothing Then
   
        Range("C1").Resize(1, Cells.Columns.Count - 2).EntireColumn.Hidden = True
       
        Range(arrColumns(Target.Row - 3)).EntireColumn.Hidden = False
   
    End If

End Sub

It Works thanks

 

[arup1032]

Thanks All for answering.

If I want to do the same thing in google Sheets then what will be the code?

 

[arup1032]

Put this code into the worksheet code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrColumns() As String

    arrColumns = Split("C:O,Q:AC,AE:AQ,AS:BE,BG:BS,BU:CG,CI:CU,CW:DI,DK:DW,DY:EK,EM:EY,FA:FM,FO:GA,GC:GO,GQ:HC,HE:HQ,HS:IE,IG:IS,IU:JG,JI:JU", ",")
   
    If Not Intersect(Target, Range("A3:A22")) Is Nothing Then
   
        Range("C1").Resize(1, Cells.Columns.Count - 2).EntireColumn.Hidden = True
       
        Range(arrColumns(Target.Row - 3)).EntireColumn.Hidden = False
   
    End If

End Sub

If I want to do this on google sheet, what will be the script?

 

[Herakles]

If I want to do this on google sheet, what will be the script?

Google Sheets does NOT use VBA code.

Google Sheets files use Apps Script, a type of javascript.

I don't program in javascript.