Pesky Autofilter Toggle

[Digory]

Help for a VBA newbie – I am taking two databases and using filters t
sort and copy data to one of 24 worksheets depending on filte
criteria. Seems to be going ok but would appreciate some advice o
autofilters. Am currently selecting row then turning on autofilter
however this seems to toggle the filter (if its already on then thi
turns it off & vice versa) so if an autofliter has been left o
somewhere in workbook its back to debugger. How do I switch it of
rather than toggle off ?

I am also using this macro to set the print area for each of my 2
worksheets by first estimating the likely number of rows (columns ar
fixed) but as my database grows I am mindful that I will need to kee
resetting this value. Can I use the COUNTA function to count rows s
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cel
value and make that cell really stand out (perhaps flash) ? Am usin
conditional formatting (three conditions) for row colours but nee
another condition to highlight specific cell entries.

Many thanks
D:-

 

[Glen Mettler]

FILTER - this works for me:
MyTab = ActiveSheet.Name
Set w = Worksheets(MyTab)
'if autofilter is on, it will turn if off otherwise it leaves it on
If w.AutoFilterMode Then
Selection.AutoFilter
End If

PRINT AREA - ROWS:
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

CONDITIONAL FORMATING:
No specific formual but you could set the conditional formating on an
adjacent cell to set background/forecolor based on the value in the cited
cell.

Glen

 

[keepITcool]

Both work for me

Testing for autofiltermode test if the "dropdown arrows are visible..
(programmatically you can set an autofilter without showing the dropdowns)

Thus you'll need to TEST on the filtermode.

If ActiveSheet.FilterMode Then
ActiveSheet.FilterMode = False
End If


To remove the SHEET's autofilter you COULD also call
RANGE's autofilter method with NO argument

Range("a1").autofilter

 

[keepITcool]

pesky editing..

you'll need to TEST on filtermode but SET sheet's autofiltermode...
(filtermode is readonly)

If ActiveSheet.FilterMode Then
ActiveSheet.AutoFilterMode = False
End If

 

[Dave Peterson]

Activesheet.autofiltermode = false

will turn autofilter off if it's on and won't do anything if it's already off.

If you're using a macro to set the print area, you could use something like:

dim LastRow as long
dim wks as worksheet

for each wks in activeworkbook.worksheets
with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
.pagesetup.printarea = .range("a1:x" & lastrow).address(external:=true)
end with
next wks

But if you don't specify any printarea, doesn't excel keep good track of what
should be printed for you?


If you're using conditional formatting, then those conditional formats will
"beat" any "normal" formatting you've put on the cell. And since you only get 3
CF's, I think you'll have to use an alternative approach.

Maybe you could apply the formatting inside some event (worksheet_change or
worksheet_calculate)???

Or maybe you could find the cell in your macro and then just issue a msgbox with
some sort of warning in it??