Unhide column dependent on autofilter selection

**rebmab** · 07-15-2011, 06:48 AM

Hi all,

I was wondering if I could ask for some help, basically is it possible to unhide a column dependent on what I choose from an autofilter. In this case all I need is to select the number 8 from the autofilter (which consists of numbers 1:9) and it to unhide columns S:U.

So when I clear the autofilter or choose a different number from the filter the columns S:U automatically hide themselves again.

I hope I have made myself clear if not please say.

Thanks any help will be much appreciated as it has been bugging me for ages!

reb

**realniceguy5000** · 07-15-2011, 07:41 AM

Hi,

Here is one way,

In ThisWorkBook Mod Place this open workbook code

Private Sub Workbook_Open()
     With Application
          .Calculation = xlAutomatic
          .MaxChange = 0.001
     End With
     ActiveWorkbook.PrecisionAsDisplayed = False
 
     Range("IE65000").Formula = "=NOW()"
     MsgBox "Workbook has been set to calculate automatically"
End Sub

Then Place This in the worksheet your filter is on.

 
Option Explicit
Private Sub Worksheet_Calculate()
Dim wks As Worksheet, i As Long
Set wks = ActiveSheet
With wks.AutoFilter
    With .Filters
        For i = 1 To .Count
            With .Item(i)
                If .On Then
 
                    If .Criteria1 Like "*8" Then
                        Columns("S:U").Hidden = False
                        Exit Sub
                    Else
                        Columns("S:U").Hidden = True
                    End If
                End If
            End With
        Next
   End With
End With
End Sub

This will force the sheet to calculate forcing the script to fire. Here is a test book as well.

**rebmab** · 07-18-2011, 06:11 AM

Hi Real -

Thanks that was fantastic really helpfull - quick question if I click on for example filter selection 8 - and it unhides the corresponding columns fantastic! Then I click on select all within the filter or clear the filter so that it shows all numbers 1 to 9 it continues to show unhidden columns instead of hiding them again. Is there a way to prevent this from happening without having to click on a seperate number first and then selecting all - if that makes sense?

Thanks so much for all your help

Reb

**rebmab** · 07-18-2011, 09:25 AM

Re: Above - I am now having a run time error 13 when I and it highlights this part of the code: If .Criteria1 Like "*8" when I input the code into my worksheet. I have multiple autofilters however only need it to funvtion on one I think thats may be why I am getting the error is there a way to specify the filter cell within the code?

Thanks sorry to keep bugging you with questions

Reb

**rebmab** · 07-18-2011, 09:42 AM

Hi Real - me again, I even get the runtime error on your sheet when I select multiple options in the autofilter including number 8. For example select 8,5,4,3,2,1 and i get a runtime error 13 and it highlights this part of the code: If .Criteria1 Like "*8"

Thanks

Reb