Help W/Small Code . .

[Evagrius Ponticus]

Hi All,

I have this code that works almost as I want it. I have a sheet that is autofiltered. Once a user autofilters columns 20, I would like to hide the arrow for that column so the user can no longer autofilter using that column. When I run the code - it hides the arrow in column 20, but then it unfilters all the data . . . can I hide the arrow in column 20 but keep the data as it was filtered before the code ran?? Thanks,

Dim c As Range
Dim i As Integer
i = Cells(2, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(2, 1), Cells(1, i))
 If c.Column = 20 Then
  c.AutoFilter Field:=c.Column, _
    Visibledropdown:=False
 End If
Next

End Sub

 

[texasalynn]

what about making the row height a size 1
Rows("1:1").RowHeight = 1

 

[Evagrius Ponticus]

Hmmm - not a bad idea - but I want the user to filter using all columns except 20, so I suppose I could just hide that column . . .not the row . .

 

[Colin Legg]

Which column does the autofilter start in? Column A?

 

[Colin Legg]

Here's an example which hides the filter in column T:

Option Explicit

'this example hides the autofilter filter in column T
Sub foo()
    Dim shp As Shape
    Dim iFilterToHide As Integer
    
    With Sheet1
        
        '---------------------------------------------
        'let's set up an autofilter for this example
        .AutoFilterMode = False
        
        With .Range("G1:U1")
            .AutoFilter
            .AutoFilter field:=14, Criteria1:="=1"
        End With
        '----------------------------------------------
        
        
        
        'dynamically determine which autofilter column intersects with column T
        iFilterToHide = .Columns("T:T").Column - .AutoFilter.Range.Cells(1).Column + 1
        For Each shp In .Shapes
        
            'is it an autofilter filter?
            If shp.Type = msoFormControl Then
                If shp.FormControlType = xlDropDown Then
                
                    'we want to hide the filter in column T
                    If shp.ZOrderPosition = iFilterToHide Then
                        shp.Visible = msoFalse
                        Exit For
                    End If
                End If
            End If
        Next shp
    End With
    
End Sub

Does that help?

 

[Evagrius Ponticus]

Hi Colin - I can't get it to work. I updated your code to meet my criteria. I stepped through it but it never seems to get to 20 so it can't hide that arrow. The filter range is A2-AR2. Once this code filters column 20, then the code should hide the arrow in column 20 so it can't be filtered manually . . .

'this example hides the autofilter filter in column T
Sub foo()
    Dim shp As Shape
    Dim iFilterToHide As Integer
    Set Myactive = Sheets("Active Projects")
    
    With Myactive
        
        '---------------------------------------------
        'let's set up an autofilter for this example
        .AutoFilterMode = False

        With .Range("A2:AR2")
            .AutoFilter
            .AutoFilter field:=20, Criteria1:="=Amortized Rate"
        End With
'        '----------------------------------------------
        
        
        
        'dynamically determine which autofilter column intersects with column T
        iFilterToHide = .Columns("T:T").Column - .AutoFilter.Range.Cells(1).Column + 1
        For Each shp In .Shapes
        
            'is it an autofilter filter?
            If shp.Type = msoFormControl Then
                If shp.FormControlType = xlDropDown Then
                
                    'we want to hide the filter in column T
                    If shp.ZOrderPosition = iFilterToHide Then
                        shp.Visible = msoFalse
                        Exit For
                    End If
                End If
            End If
        Next shp
    End With
    
End Sub

 

[Colin Legg]

Hi BT

Are there any other shapes on the worksheet?

Perhaps this?

Sub foo()
    Dim shp As Shape
    Dim iFilterToHide As Integer
    Dim iCounter As Integer
    
    With Sheets("Active Projects")
        
        '---------------------------------------------
        'let's set up an autofilter for this example
        .AutoFilterMode = False
        With .Range("A2:AR2")
            .AutoFilter
            .AutoFilter field:=20, Criteria1:="=Amortized Rate"
        End With
'        '----------------------------------------------
        
        
        
        'dynamically determine which autofilter column intersects with column T
        iFilterToHide = .Columns("T:T").Column - .AutoFilter.Range.Cells(1).Column + 1
        For Each shp In .Shapes
        
            'is it an autofilter filter?
            If shp.Type = msoFormControl Then
                If shp.FormControlType = xlDropDown Then
                    iCounter = iCounter + 1
                    'we want to hide the filter in column T
                    If iCounter = iFilterToHide Then
                        shp.Visible = msoFalse
                        Exit For
                    End If
                End If
            End If
        Next shp
    End With
    
End Sub

 

[Evagrius Ponticus]

Hi Colin_L,

Great Job!!!! That worked flawlessly! I am really, really grateful.

Yes - I do have a number of commandbuttons on the sheets - why should that make a difference?? I will spend some time stepping through it to learn what I can - I wish I knew how you guys on this board get so good at VBA

 

[Colin Legg]

I had a feeling that was the problem. If there are other shapes on the worksheet then that will make a difference because it could impact the filter shape's ZOrderPosition property (ie. the 20th filter might not have a ZOrderPosition of 20).

 

[Evagrius Ponticus]

Colin - sorry for the questions . .but I wonder if you could clarify something.

This is the only part I don't yet understand.

     iFilterToHide = .Columns("T:T").Column - .AutoFilter.Range.Cells(1).Column + 1

iFilterToHide = 20 in the code. .Columns("T:T").column = 20. I don't understand the other portion. I placed the msgboxes below in the code - and I got 20 for the first msgbox and 2 for the second. So 20-2 is 18 - how is iFilterToHide getting 20 - even though that's the right answer. Thanks . .

MsgBox .Columns("T:T").Column + 0
MsgBox .AutoFilter.Range.Cells(1).Column + 1