Hi All,
I'm trying to work out a way how to hide cols dependent on autofilter selection but have come to a point in scratching my head.
So far I have this:
Private Sub Worksheet_Calculate()
Dim rng As Range, Rng1 As Range
Dim iCol As Long
Sheets("Telephony MTD").Select
Range("B4").Select
iCol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(iCol))
If rng Is Nothing Then Exit Sub
Set rng = Range(ActiveCell.Offset(1, _
iCol - ActiveCell.Column), rng(rng.Count))
On Error Resume Next
Set Rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not Rng1 Is Nothing Then
Rng1(1).Select
End If
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B4").Select
If Range("A1").Value = "Centre Manager" Then
Columns("D").EntireColumn.Hidden = True
Else
If Range("A1").Value = "Manager" Then
Columns("D:E").EntireColumn.Hidden = True
Else
If Range("A1").Value = "Team Leader" Then
Columns("D:F").EntireColumn.Hidden = True
Else
If Range("A1").Value = "Consultants" Then
Columns("D:F").EntireColumn.Hidden = True
Else
End If
End If
End If
End If
End Sub
With a =now() in another cell to trigger the calculation when autofiltering
But it seems to get caught in a never ending loop and can't work out a way forward.
If anyone can help me or suggest an alternative way to code it to hide cols based on autofilters i'd be most greatful
Bookmarks