CODEWITHSUNDEEP
excelvba
ExcelNoobie
ExcelNoobie

Reputation: 1

VBA to sort Dynamic data range by Last Column (specific rows)

Appreciate if someone could advise how do I SORT DYNAMIC DATA (being the filled last column can vary each month) BY LAST COLUMN FROM SPECIFIC ROW (specifically ROW 5).

I read that VBA to sort last column could use:

Sub SortLastColumn()
    With ActiveSheet.UsedRange
        Columns(.Columns(.Columns.Count).Column).Sort key1:=Columns(.Columns(.Columns.Count).Column)
    End With
End Sub

But this return all my data starting from Row 1. I am not sure how i could have a work around.

Look forward to a favourable solution. Thank you!

Upvotes: 0

Views: 698

Answers (2)

StoneGiant
StoneGiant

Reputation: 1497

You need to exclude the first 4 rows from your sort range.

So, walking through it, first define the range you want to sort.

Sub SortLastColumn()

    Dim sortRange As Range

    Set sortRange = Range( "A5", Cells( ActiveSheet.UsedRange.Rows.Count, _
                                        ActiveSheet.UsedRange.Columns.Count ) )
    ' Now do the sorting

    sortRange.Sort Key1:= Columns(ActiveSheet.UsedRange.Columns.Count)

End Sub

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

Sort: CurrentRegion vs UsedRange

Microsoft

A Thought

  • Although the code for the CurrentRegion version is a little more complicated and forces you to specify the address of the first cell of the data, I prefer it because it allows you to have other data in the worksheet, not just the 'table'.

The Code

Option Explicit

Sub sortByLastColumnCR()
    
    Const FirstCellAddress As String = "A1"
    Const FirstSortRow As Long = 5
    
    ' Define Complete Range.
    Dim rng As Range
    Set rng = ActiveSheet.Range(FirstCellAddress).CurrentRegion
    ' Get rid of possible data to the left and above of first cell
    ' if First Cell Address is differenct than "A1".
    Set rng = ActiveSheet.Range(FirstCellAddress, rng.Cells(rng.Rows.Count, _
                                                            rng.Columns.Count))
    
    ' Define Sort Range.
    Set rng = rng.Resize(rng.Rows.Count - FirstSortRow + 1) _
                 .Offset(FirstSortRow - 1)
    
    ' Sort.
    rng.Sort Key1:=rng.Cells(1, rng.Columns.Count), _
             Order1:=xlAscending

End Sub

Sub sortByLastColumnUR()
    
    Const FirstSortRow As Long = 5
    
    ' Define Complete Range.
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    ' Define Sort Range.
    Set rng = rng.Resize(rng.Rows.Count - FirstSortRow + 1) _
                 .Offset(FirstSortRow - 1)
    
    ' Sort.
    rng.Sort Key1:=rng.Cells(1, rng.Columns.Count), _
             Order1:=xlAscending

End Sub

Upvotes: 1

Related Questions