View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Frank Kabel Frank Kabel is offline
external usenet poster
 
Posts: 3,885
Default Sorting a Filtered List

Hi
one way in VBA: First storing the autofilter settings in your current
worksheet, applying the sort, restoring the filter settings.
Note: in the following example i have just inserted a quite simple,
recorded sort statement - adapt this to your needs
Try (Saving and Restoring the filter - copied from the Excel help)

Sub Sort_with_filters()
Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String
Dim f As Integer
Dim col As Integer
Set w = ActiveSheet

'Save current Autofilter settings
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With
w.AutoFilterMode = False

'do the sorting just an example - change this to your needs
Range("A1:C4").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


'Restore the filter
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If filterArray(col, 2) Then
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Alex J wrote:
All,
I have found that applying a Sort command to a Filtered list anly
seems to sort the visible (non filtered) rows.
Is there a way (in VBA) to apply the sort to all of the rows in the
field, including the hidden ones, without removing and then
re-applying the filter?

Alex J