PDA

View Full Version : Solved: AutoFilters: need simple tutorial, and differences (?) for Mac users?



GreenTree
04-07-2007, 06:42 PM
Not really a VBA question per se, but sort of related. I'm using one spreadsheet with lots of VBA code to generate a second spreadsheet the I distribute to a small audience (15-20 people). That second spreadsheet has no macros or VBA code in it, but does use AutoFilter, and not everyone who needs to use the spreadsheet is particularly familiar with AutoFilters.

First question: is there an on-line tutorial for using (as in, selecting things with the drop-down boxes, NOT as in making spreadsheets that take advantage of) AutoFilters that would be useful for people who are not highly experienced with Excel? Nobody needs to manipulate the spreadsheet using anything more complicated than selecting various criteria and then sorting with the autofilter, but that's unfamiliar for some people. Things like "I select 'Top 10' on this column, but all my rows disappear" (since the filters applied on other columns have already removed all the rows that contain the top 10 results of that column), are giving guys fits. If there's a tutorial or help page out there pitched at that level, I'd be most appreciatiave for any links.

Second question: do autofilters work any differently in the Mac version of Excel? Particularly, one individual who works on a Mac doesn't seem to be finding the "Sort Ascending" and "Sort Descending" options from the autofilter drop-down. Yes, I can talk him through Data|Sort if I have to, but if he should be seeing those options on the AutoFilter, I'd much rather get that working for him. Alternatively, if every Mac user is without those options within the autofilter, I'd like to highlight that limitation for users ahead of time.

Thanks for your help!

GT

Aussiebear
04-07-2007, 08:37 PM
Google search will bring up a number of sites, but you might like to try
http://www.ozgrid.com/vba/Autofilter-vba.htm as a starting point.


Sorry, I can get there after googling, but not by direct linkage.

Aussiebear
04-07-2007, 08:42 PM
Here it is for your perusal,

VBA & AutoFilters
AutoFilter provides us with a MUCH faster alternative to loops (http://www.ozgrid.com/VBA/loops.htm) of all kinds.
In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learnt Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.
Sub CheckForAutoFilters()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "They are visible"
Else
MsgBox "They are not visible"
End If
End Sub From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range. Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.
Sub CheckForAutoFilters2()

With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox "They are visible and in use"
ElseIf .AutoFilterMode = True Then
MsgBox "They are visible but not in use"
Else
MsgBox "They are not visible or in use"
End If
End With

End Sub As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down. So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use. However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.
Sub ApplyAutoFilters()

With ActiveSheet
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
End With

End Sub Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter. If we are to first check the range that AutoFilter is applied to, we would use code like below;
Sub IsAutoFiltersOnRightRange()

With ActiveSheet
If .AutoFilterMode = True Then
MsgBox .AutoFilter.Range.Address
Else
MsgBox "AutoFilters are not on"
End If
End With

End Sub In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters. Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;
Sub ApplyAutoFiltersToOneCell()

With ActiveSheet
.AutoFilterMode = False
.Range("A1:A2").AutoFilter
End With

End Sub

GreenTree
04-08-2007, 10:42 AM
Hi AussieBear,

Thank you for that link & article, although what I'm looking for is a couple of steps less advanced than that. The target audience for the spreadsheet I'm sending out is, mostly, far less advanced in Excel than actually writing any VBA code, and I'm not trying to bring them up to that point. What I'm looking for is a tutorial for them, explaining how to use, not how to implement, the autofilters. Things like how to use a filter, how to sort, how to remove a filter, what happens when you have multiple filters applied at once, things like that. I understand these concepts, but I'm not a best at explaining, and I was hoping that someone might know of a good tutorial or explanation that is already available on line. Heavy on the pictures, short sentences, small words, but light, very light, on VBA and advanced Excel concepts! (Not taking shots at the folks who are looking at the spreadsheet; they're plenty smart, just not about Excel.)

Thanks,

GT

mdmackillop
04-08-2007, 10:49 AM
Check out some of our Resources sites such as this (http://www.contextures.com/tiptech.html)

GreenTree
04-10-2007, 06:18 AM
Thanks, that looks like some very good stuff!

Marking solved.