Autofilter - on select columns

[Guest]

You all might begin to wish I never found this forum. But the help truly is
appreciated.

I have created a list which naturally turned on the autofilter feature. How
can I turn off the autofilter feature for all but two of the columns?

Thanks
Rudy

 

[Anne Troy]

Autofilter works on all contiguous columns. If you don't want a column to be
included, you need a blank column in between, which I strongly DO NOT
recommend since other Excel features won't work right, such as sorting.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[Debra Dalgleish]

You can use programming to hide some of the dropdown arrows. For example:

'===========================
Sub HideSomeArrows()
'hide some autofilter arrows
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
Select Case c.Column
Case 2, 3
c.AutoFilter Field:=c.Column, _
Visibledropdown:=True
Case Else
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End Select
Next
Application.ScreenUpdating = True
End Sub
'==============================

 

[Guest]

Debra,

Thanks You for the help. I could not have gotten as far as I have without
your help. Not only have you helped me here but from your website as well.
Someone here recommended a website

http://www.contextures.com

It turns out that it is yours. It was incredibly helpful. I downloaded the
sample file called "AdvFilterCity" and stole your Macro1. I made a few
changes and it works for my spreadsheet. This is remarkable because I really
know nothing of VB code. In fact I parse out the data two ways. One by
Owner and the other by Project Category. See my previous post tited: Subject:
Filterered list to new worksheet. I was able to selectively alter your macro
to work for me.

However, in the code below I don't see how to select which columns will
have the autofeature hidden.

Can you help further?

Thanks
Rudy

 

[Dave Peterson]

I bet if you share the range of data you want autofiltered (include all the
columns--not just the ones that will end up with visible arrows) and share the
columns that you want to have visible arrows, then Debra can help:

For instance:
Filter C1:X1
show arrows on D F G X

 

[Debra Dalgleish]

And in the sample code:

'========================
Case 2, 3
c.AutoFilter Field:=c.Column, _
Visibledropdown:=True
Case Else
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
'================================

Case 2, 3

means if the column is 2 or 3 (B or C) then the dropdown arrow is visible.
For every other column, the arrow is not visible.

You can add other column numbers, e.g.:

Case 2, 4, 5, 7

 

[Guest]

Debra,

I copied your macro into the Vb editor and hit a wall. Should this be a new
Macro, should it be assigned to the workbook or to the the specific
worksheet? My bet is the specific worksheet. If so, it is worksheet 5
"Project List". My list header row is in Row 7.

I tried all three and was apparently impatient. After each attempt, I
closed and reopened the workbook. My last attempt was to assign it to a
Module and assign the Macro to a text box as I did for the export data macro.
When nothing seemed to work, I waited awhile. Now when I look at the
worksheet, the drop down arrows are hidden. What triggers the Macro, if not a
button of some sort? Which attempt worked? Do I run the code once and save
the workbook thereby saving the properties set by the code? Hey, you made me
think I could do this....

Thanks again...I took Dave Petersons advice and posted more detail about by
spreadsheet....it never appeared here.....but rather than repost I decided to
work with your last reply.

A comment from my lost post was that Hogs get fed and Pigs get slaughtered
and that I would try to be a simple Hog. Let me know if I get Piggish.
However, this is a far superior way to learn than reading a book. (Is that a
back-handed compliment? - I don't mean it to be) If I ever learn enough of
this stuff I will certainly try to help others out.

I wonder how my post got lost.......???
Thanks
Rudy

 

[Debra Dalgleish]

The code goes onto a regular code module, and you can run it by
assigning it to the text box on your worksheet. After you've run it, and
saved the workbook, you shouldn't have to run it again, unless you turn
off, then reapply, the AutoFilter.

 

[Guest]

I pasted the code into module 3 and selected Run>Run/Sub user Form

I get a run-time error "1004"
AutoFlter Method of Range Class Failed

Error highlighted from code below as

---------------------------------
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
--------------------------------

But it appears to work anyway.

Module 3 is

Sub HideSomeArrows()
'hide some autofilter arrows
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
Select Case c.Column
'The next row tells ths macro which Column # gets drop downs
'Case 2, 3
Case 2, 9, 10
c.AutoFilter Field:=c.Column, _
Visibledropdown:=True
Case Else
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End Select
Next
Application.ScreenUpdating = True
End Sub

Thanks,
Rudy