Hide all autofilter arrows in a worksheet without a loop

**ndjustin20** · 01-16-2012, 06:31 PM

Hello Everyone,

I can't for the life of me remember how I did this in one of my worksheets but I did it somehow. I don't ever want to see the autofilter arrows as I am filtering using VBA.

Justin

**arlu1201** · 01-17-2012, 05:38 AM

You can add this code to your existing code where appropriate - [code]

With Worksheets(1).Range("A1:P1")
.AutoFilter Field:=1, VisibleDropDown:=False
.AutoFilter Field:=2, VisibleDropDown:=False
.AutoFilter Field:=3, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
.AutoFilter Field:=5, VisibleDropDown:=False
Etc...
End With

**ndjustin20** · 01-17-2012, 11:10 AM

This is so weird. If you run that code it doesn't work though if I run this code:


ActiveSheet.Range("A1:P1").AutoFilter Field:=1, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=2, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=3, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=4, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=5, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=6, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=7, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=8, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=9, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=10, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=11, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=12, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=13, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=14, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=15, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=16, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=17, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=18, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=19, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=20, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=21, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=22, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=23, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=24, VisibleDropDown:=False
ActiveSheet.Range("A1:P1").AutoFilter Field:=25, VisibleDropDown:=False

It works fine. What is stranger is I only have to run it once then take it out and from there the arrows never come back. There must be a property I am setting in the excel code somewhere by doing this that changes the property for the specific worksheet when it is ran. I'm not completely familiar with how excel works but this seems to be what is happening. THANK YOU FOR YOUR ASSISTANCE :-)

Justin

**arlu1201** · 01-17-2012, 11:24 AM

You can still reduce the lines of code through this -

for i = 1 to 25
     ActiveSheet.Range("A1:P1").AutoFilter Field:=i, VisibleDropDown:=False
next i

**royUK** · 01-17-2012, 11:29 AM

You should be able to restore the arrows by setting the VisibleDropDown to True

When you ran the With Worksheets(1).Range("A1:P1") code did you have the . in front of AutoFilter?

**ndjustin20** · 01-17-2012, 11:39 AM

I'm betting I missed the dot :-) Also, are there a set of settings or properties that can be turned on/off like a .ini file for php?

**royUK** · 01-17-2012, 11:46 AM

I'm not sure what you mean. There's an object browser that might help

**ndjustin20** · 01-17-2012, 11:52 AM

Ok let me reword that.....by using


ActiveSheet.Range("A1:P1").AutoFilter Field:=25, VisibleDropDown:=False

I was able to turn off or set the 25th column's VisibleDropDown property to FALSE. Is there a list of Rows/Column properties that can be manipulated like this was?