Excel VBA Based on filter value, only show columns ...

[Excelquestion35]

Hi all,

Currently I am looking for a clever way to hide columns based on a value in column E from the below example. I have already looked for some ideas but can't find the exact one I am looking for.
As you can see, the following sheet contains a lot of columns, the user is overloaded with data.
The below sheets shows all current possible activities for all departments combined. Thus, not all these activities belong to one department alone.

As soon as I filter on the value S2F from the department column (E), I would like to only show a subset of the columns. (e.g. AA:AH, AR:AS & AX combined)

Is there a convenient way to do this?

Also, since this is a competence and cross training matrix, would it be possible to do the same but then taking into account that some extra columns have to be visible as as soon as someone is scheduled to learn something that is outside of the scope of activities for e.g. F2S?

 

[Excelquestion35]

Bump

 

[John_w]

The problem is that there isn't a worksheet event which is fired when you change an autofilter. However, there is a workaround using the Worksheet_Calculate event and a formula whose result is based on cells in the data range.

In this example, cell A1 contains a formula which sums numeric values in column A of the data range.

Example.xlsm
	A
1	611
Data

Cell Formulas
Range	Formula
A1	A1	=SUM(A11:A36)

Put this code in the code module of the Data sheet:

Private Sub Worksheet_Calculate()
    Hide_Department_Columns Me
End Sub


Public Sub Hide_Department_Columns(dataSheet As Worksheet)

    Dim Department As String
    
    With dataSheet
    
        If .AutoFilterMode Then
        
            Department = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(1, 5).Value  '5 is column E
        
            Application.ScreenUpdating = False
            
            Select Case Department
            
                Case "D2S"
                    .Columns("A:Z").Hidden = False  'unhide all columns
                    .Range("J:L,O:P,S:S").EntireColumn.Hidden = True
                
                Case "S2F"
                    .Columns("A:Z").Hidden = False
                    .Range("M:P,S:S,V:W").EntireColumn.Hidden = True
            
                Case Else
                    .Columns("A:Z").Hidden = False
                
            End Select
            
            Application.ScreenUpdating = True
        
        End If
        
    End With

End Sub

Change the columns to hide and add more Case statements to the Select Case Department statement, as needed.

 

[Excelquestion35]

Hi John,

Thank you for your thorough explanation and provided code. Unfortunately, it is not working yet. I have tried adjusting the following cells without success:

Department = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(10, 5).Value  '5 is column E

and

Department = .AutoFilter.Range.Offset(10, 5).SpecialCells(xlCellTypeVisible)(10, 5).Value  '5 is column E

I have also added the sum formula to cell A1 that is counting the sum from A11 onwards.

Is there anything I did wrong?

 

[John_w]

First, is the Worksheet_Calculate event being fired? To find out, change it to:

Private Sub Worksheet_Calculate()
    MsgBox "Worksheet_Calculate"
    Hide_Department_Columns Me
End Sub

And you should see the message displayed every time the Worksheet_Calculate event occurs.

I used the =SUM(A11:A36) formula for my convenience, with A11:Z36 containing my test data. From your image, I counted row 11 as the first data row, below the column headings. I see your column A probably doesn't contain numeric values, so you should try a different formula in A1 (or any spare cell outside your data range) , for example =SUBTOTAL(3,E11:E36) counts the number of visible rows.

The original line:

            Department = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(1, 5).Value  '5 is column E

should be correct. It should read the first visible cell in column E in the autofiltered rows, which is the Department name you are autofiltering on.

 

[Excelquestion35]

Thank you, it seems to work now!

Is there by any chance also a way to unhide the columns or reset them in someway when filtering again? For instance, when I first select department D2S it does the selection, but immediately filtering on another department reduces the visible columns to not the actual selection as provided in the code. (Example below)

I have a general unhide columns button in the sheet which does the trick but I was wondering whether it was possible to insert a step where all columns are unfiltered as soon as we change the tickbox.

Here I first filtered on department F2S which showed the correct columns, but the wrong selection remains as soon as I untick F2S and select EHV1.

 

[John_w]

Here I first filtered on department F2S which showed the correct columns, but the wrong selection remains as soon as I untick F2S and select EHV1.

Have you added a Case "EHV1" statement to hide the specific columns for EHV1?

Something like this modified routine, which also first unhides all columns A:Z and then hides the specific columns for each Department.

Public Sub Hide_Department_Columns(ws As Worksheet)

    Dim Department As String
    
    Application.ScreenUpdating = False
   
    With ws
    
        'Unhide all columns
        
        .Columns("A:Z").Hidden = False
    
        If .AutoFilterMode Then
        
            Department = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(1, 5).Value  '5 is column E
            
            'Hide specific columns depending on filtered Department
            
            Select Case Department
                Case "D2S"
                    .Range("J:L,O:P,S:S").EntireColumn.Hidden = True
                Case "S2F"
                    .Range("M:P,S:S,V:W").EntireColumn.Hidden = True
                Case "F2S"
                    .Range("K:P,R:S,X:Z").EntireColumn.Hidden = True
                Case "EHV1"
                    .Range("J:K,M:M,T:V,Y:Y").EntireColumn.Hidden = True
                Case Else
                    MsgBox "Not written code to hide columns for Department '" & Department & "'"
            End Select
                    
        End If
        
    End With

    Application.ScreenUpdating = True

End Sub

 

[Excelquestion35]

Sorry for the late reply, thank you, that did the trick!