Hiding Columns using VBA

**shiftyspina** · 07-01-2016, 04:48 AM

Hi

I'm trying to hide specific columsn in my worksheet based on the value in cell B3

This is my code at the moment

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'To Hide Columns for Machine Report
If wSheet.Range("B3") = "Machine" Then
Machine_Lines.Columns("F:Q").EntireColumn.Hidden = True
Machine_Lines.Columns("AB:AE").EntireColumn.Hidden = True
Machine_Lines.Columns("AN:AS").EntireColumn.Hidden = True
End If

'To Hide Columns for Hand Lines Report
If wSheet.Range("B3") = "Hand" Then
Machine_Lines.Columns("F:F").EntireColumn.Hidden = True
Machine_Lines.Columns("K:AB").EntireColumn.Hidden = True
Machine_Lines.Columns("AM:AR").EntireColumn.Hidden = True
End If

End Sub

But it doesn't seem to work?

Am I missing something? I need it to hide the certain columns every time cell b3 is changed as there are a lot of helper columns in the sheet which make for messy reading and reporting

Thanks

Dan

**Kamboj** · 07-01-2016, 05:39 AM

use this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
'To Hide Columns for Machine Report
If Range("B3").Value = "Machine" Then
Sheets("Machine_Lines").Columns("F:AS").EntireColumn.Hidden = False ' to unhide col
Sheets("Machine_Lines").Columns("F:Q").EntireColumn.Hidden = True
Sheets("Machine_Lines").Columns("AB:AE").EntireColumn.Hidden = True
Sheets("Machine_Lines").Columns("AN:AS").EntireColumn.Hidden = True
End If
'To Hide Columns for Hand Lines Report
If Range("B3") = "Hand" Then
Sheets("Machine_Lines").Columns("F:AS").EntireColumn.Hidden = False ' to unhide col
Sheets("Machine_Lines").Columns("F:F").EntireColumn.Hidden = True
Sheets("Machine_Lines").Columns("K:AB").EntireColumn.Hidden = True
Sheets("Machine_Lines").Columns("AM:AR").EntireColumn.Hidden = True
End If
End If
End Sub