Sorting with VBA

JazzzyJo

Board Regular
Joined
Jul 12, 2011
Messages
60
Hello. First time I post here in a long time. Thank you in advance for your precious help.
I have the following code. It was created to use a button to sort column C from 20 to lastRow and it does work.
I now want to use the same code to sort in function of column E instead of C so I change the C for an E in of ActiveSheet.Sort.SortFields.Add2 Key:=Range("C19:C" & lastRow...........
But the macro is still sorting the data in function of column C and not column E. What am I doing wrong.

Sub SortDateFDE()
Dim lastRow As Long
Dim columnFDE As String
Dim decoderFDE As String
Dim sortField As sortField

decoderFDE = "DecoderFDE"
columnFDE = "E"
lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row

' Get the current sort settings for the column
On Error Resume Next
Set sortField = ActiveSheet.Sort.SortFields(1)
On Error GoTo 0

If sortField Is Nothing Then
' If the column is not sorted, sort in ascending order
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range("E19:E" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Else
' If the column is already sorted, reverse the sort order
If sortField.SortOn = xlSortOnValues Then
If sortField.Order = xlAscending Then
sortField.Order = xlDescending
Else
sortField.Order = xlAscending
End If
End If
End If

With ActiveSheet.Sort
.SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
With ActiveSheet.Sort
.SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data <--------------Should this be column E?
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 
Upvote 0
Welcome (again) to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
  
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
  
  If sortField Is Nothing Then
    ' If the column is not sorted, sort in ascending order
    ActiveSheet.Sort.SortFields.Clear
  Else
    ' If the column is already sorted, reverse the sort order
    With sortField
      If .SortOn = xlSortOnValues Then
        If .Order = xlAscending Then .Order = xlDescending Else sortField.Order = xlAscending
      End If
    End With
  End If
  
  ActiveSheet.Sort.SortFields.Add Key:=Range("E19:E" & lastRow), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  
  With ActiveSheet.Sort
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
With ActiveSheet.Sort
.SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data <--------------Should this be column E?
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
No because I want to sort the data from column C to N in function of the data (dates) in column E
 
Upvote 0
Welcome (again) to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If sortField Is Nothing Then
    ' If the column is not sorted, sort in ascending order
    ActiveSheet.Sort.SortFields.Clear
  Else
    ' If the column is already sorted, reverse the sort order
    With sortField
      If .SortOn = xlSortOnValues Then
        If .Order = xlAscending Then .Order = xlDescending Else sortField.Order = xlAscending
      End If
    End With
  End If
 
  ActiveSheet.Sort.SortFields.Add Key:=Range("E19:E" & lastRow), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
  With ActiveSheet.Sort
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
THank you for taking the time.
It still sort the data in function of column C. It does not the date in column E.

But it does sort properly all the date from column C to N from row 20 to whatever the amount of row available (it's variable).
Which means that the SetRange Range ("C19:N"& lastRow..... is doing what it needs to do.
 
Upvote 0
Pleae try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  Dim nOrder As Long
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If Not sortField Is Nothing Then
    ' If the column is already sorted, reverse the sort order
    If sortField.SortOn = xlSortOnValues Then
      If sortField.Order = xlAscending Then nOrder = xlDescending Else nOrder = xlAscending
    End If
  End If
 
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E20:E" & lastRow), SortOn:=xlSortOnValues, Order:=nOrder, DataOption:=xlSortNormal
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
 
Last edited:
Upvote 0
Solution
Pleae try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  Dim nOrder As Long
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If Not sortField Is Nothing Then
    ' If the column is already sorted, reverse the sort order
    If sortField.SortOn = xlSortOnValues Then
      If sortField.Order = xlAscending Then nOrder = xlDescending Else nOrder = xlAscending
    End If
  End If
 
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E19:E" & lastRow), SortOn:=xlSortOnValues, Order:=nOrder, DataOption:=xlSortNormal
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
Oh. That might just be it. I’m done now but will try it later. Thank you very much.
 
Upvote 0
ha
Pleae try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  Dim nOrder As Long
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If Not sortField Is Nothing Then
    ' If the column is already sorted, reverse the sort order
    If sortField.SortOn = xlSortOnValues Then
      If sortField.Order = xlAscending Then nOrder = xlDescending Else nOrder = xlAscending
    End If
  End If
 
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E20:E" & lastRow), SortOn:=xlSortOnValues, Order:=nOrder, DataOption:=xlSortNormal
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
[/CODE
[QUOTE="DanteAmor, post: 6032351, member: 439277"]
Pleae try this:

[CODE=vba]
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  Dim nOrder As Long
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If Not sortField Is Nothing Then
    ' If the column is already sorted, reverse the sort order
    If sortField.SortOn = xlSortOnValues Then
      If sortField.Order = xlAscending Then nOrder = xlDescending Else nOrder = xlAscending
    End If
  End If
 
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E20:E" & lastRow), SortOn:=xlSortOnValues, Order:=nOrder, DataOption:=xlSortNormal
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
I had to try it. It works. Thank a bunch. You did in 30 minutes what i was not able to do in two days.
Thank you very much.
[/QUOTE]
 
Upvote 1
Pleae try this:

VBA Code:
Sub SortDateFDE()
  Dim lastRow As Long
  Dim columnFDE As String, decoderFDE As String
  Dim sortField As sortField
  Dim nOrder As Long
 
  decoderFDE = "DecoderFDE"
  columnFDE = "E"
  lastRow = Sheets(decoderFDE).Cells(Rows.Count, columnFDE).End(xlUp).Row
 
  ' Get the current sort settings for the column
  On Error Resume Next
  Set sortField = ActiveSheet.Sort.SortFields(1)
  On Error GoTo 0
 
  If Not sortField Is Nothing Then
    ' If the column is already sorted, reverse the sort order
    If sortField.SortOn = xlSortOnValues Then
      If sortField.Order = xlAscending Then nOrder = xlDescending Else nOrder = xlAscending
    End If
  End If
 
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E20:E" & lastRow), SortOn:=xlSortOnValues, Order:=nOrder, DataOption:=xlSortNormal
    .SetRange Range("C19:N" & lastRow) 'replace with the range of cells containing your table data
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
One last thing. If the time is in column F, could the macro use 2 column to do the sorting. Use column E for date and F for time?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top