VBA for sorting all data in active sheet(varies in column and row length)

[cdalgorta]

Hi,

Like the title says, I'm trying to make a VBA to sort "all" my data based on D column(descend). I need a VBA that works on the active sheet regardless of name(always different). Also, the number of columns and rows changes every time I run the report, so I cannot have a specific range.
Basically the same thing that would happen if I clicked on any cell on the new Data, clicked filter, clicked the arrow on D column and clicked largest to smallest. "All" data changes based on D column.

Also, I'd appreciate it a lot if you could give me the same VBA, but if I wanted to have multiple sort criteria like D column descend and then F column Descend. Just like a "custom sort with 2 levels". I don't need it right now, but I will in a few weeks.
Thank you so much in advance!

 

[johnnyL]

Try the following:

Sub SortColumn()
'
    Dim LastRowInColumn         As Long
    Dim StartRow                As Long
    Dim LastColumnInSheet       As String
'
    StartRow = 2
    LastColumnInSheet = Split(Cells(1, (Cells.Find("*", , xlFormulas, , xlByColumns, _
            xlPrevious).Column)).Address, "$")(1)                                               ' Returns a Column Letter
    LastRowInColumn = Range("D" & Rows.Count).End(xlUp).Row                                     ' Returns a Row Number
'
    Range("A" & StartRow & ":" & LastColumnInSheet & LastRowInColumn).Sort _
            Key1:=Range("D" & StartRow), Order1:=xlDescending, Header:=xlNo                      ' Sort Data by Coulmn D
End Sub

 

[cdalgorta]

Try the following:

Sub SortColumn()
'
    Dim LastRowInColumn         As Long
    Dim StartRow                As Long
    Dim LastColumnInSheet       As String
'
    StartRow = 2
    LastColumnInSheet = Split(Cells(1, (Cells.Find("*", , xlFormulas, , xlByColumns, _
            xlPrevious).Column)).Address, "$")(1)                                               ' Returns a Column Letter
    LastRowInColumn = Range("D" & Rows.Count).End(xlUp).Row                                     ' Returns a Row Number
'
    Range("A" & StartRow & ":" & LastColumnInSheet & LastRowInColumn).Sort _
            Key1:=Range("D" & StartRow), Order1:=xlDescending, Header:=xlNo                      ' Sort Data by Coulmn D
End Sub

Thank you so much! It worked perfectly. May I know what would I need to do if I wanted a 2 level sorting for the F column as the 2nd level?

 

[johnnyL]

Thank you so much! It worked perfectly. May I know what would I need to do if I wanted a 2 level sorting for the F column as the 2nd level?

Sub SortTwoColumns()
'
    Dim LastRowInColumn         As Long
    Dim StartRow                As Long
    Dim LastColumnInSheet       As String
'
    StartRow = 2
    LastColumnInSheet = Split(Cells(1, (Cells.Find("*", , xlFormulas, , xlByColumns, _
            xlPrevious).Column)).Address, "$")(1)                                               ' Returns a Column Letter
    LastRowInColumn = Range("D" & Rows.Count).End(xlUp).Row                                     ' Returns a Row Number
'
    Range("A" & StartRow & ":" & LastColumnInSheet & LastRowInColumn).Sort _
            Key1:=Range("D" & StartRow), Order1:=xlDescending, _
            Key2:=Range("F" & StartRow), Order1:=xlDescending, Header:=xlNo                     ' Sort Data by Coulmn D & F
End Sub

 

[johnnyL]

Key2 has been corrected now.

 

[cdalgorta]

Key2 has been corrected now.

Thank you so much once again!

 

[johnnyL]

Glad to help.