Sorting with VBA

J

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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
Skyybot said:
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
Click to expand...
No because I want to sort the data from column C to N in function of the data (dates) in column E
 
Upvote 0
DanteAmor said:
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
----- --
Click to expand...
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
DanteAmor said:
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
Click to expand...
Oh. That might just be it. I’m done now but will try it later. Thank you very much.
 
Upvote 0
ha
DanteAmor said:
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
Click to expand...
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
DanteAmor said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

H
  • Solved
Column sort
Replies
9
Views
519
Fluff
Fluff
F
  • Solved
Sorting by two columns across all tabs- VBA
2
Replies
14
Views
576
frustrated_macro
F
G
  • Question Question
Sort (Z-A) Macro not working
Replies
1
Views
94
NateSC
NateSC
T
  • Solved
Can I sort a table by applying columns in a different order to the sort request?
Replies
5
Views
204
TheRedCardinal
T
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
271
RoryA
RoryA

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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