Sorting a Filtered List

  • Thread starter Thread starter Alex J
  • Start date Start date
A

Alex J

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
 
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
 
Frank,
Thanks for that. I will be testing tonight!
Regards,
AlexJ
-----Original Message-----
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"),
Click to expand...
Order1:=xlAscending, Header:= _
 
Frank,
Worked like a charm! Thanks for your help. (I had
attempted this before but did not succeed).

Alex J
-----Original Message-----
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"),
Click to expand...
Order1:=xlAscending, Header:= _
 
Back
Top