Using Autofilter More Than Once

[Paul Moles]

I understand it is not possible to use
Data,Filter,Autofilter more than once on a worksheet.

I want a macro to swap the autofilter area between named
ranges. I am running into difficulty because the
autofilter may not be in use on any range in which case
select range then apply autofilter. Alternatively another
range maybe filtered in which case switch off autofilter
for this range select the new range and apply autofilter.

It could even happen that a user doesn't realise they are
trying to re apply autofilter to current area. In which
case status quo remains do nothing.

All easy enough when sitting at the machine myself but not
so easy for users.

Many Thanks

Paul Moles

 

[Debra Dalgleish]

You could use event code to set the AutoFilter. For example:

'======================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim r As Long
If Not ActiveSheet.AutoFilterMode Then
Target.AutoFilter
Exit Sub
End If

Set rng = ActiveSheet.AutoFilter.Range
If Intersect(Target, rng) Is Nothing Then
ActiveSheet.AutoFilterMode = False
On Error Resume Next
Target.AutoFilter
End If

End Sub
'=========================

 

[Paul Moles]

This is useful but not quite what I want, I want to be
able to apply the autofilter to a named range on demand
rather than wherever and whenever the cursor happens to
click. Sometimes it is necessary to leave one range
autofiltered whilst working on other areas of the sheet.

Many Thanks

Paul Moles

 

[Debra Dalgleish]

If you have a data validation list of range names in cell I2, you could
use the Worksheet_Change event:

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Address = "$I$2" Then
On Error Resume Next
If Not ActiveSheet.AutoFilterMode Then
Range(Target.Value).AutoFilter
Else
Set rng = ActiveSheet.AutoFilter.Range
If Intersect(Range(Target.Value), rng) Is Nothing Then
AutoFilterMode = False
Range(Target.Value).AutoFilter
End If
End If
End If
End Sub
'===============================