VBA code to sort a variable length range

[ChartMaster]

I am new to VBA and am trying to use the macro recorder to create the code to sort a range which can have a variable number of rows. I have tried using the relative addressing mode when recording the procedure, but the resulting code always uses the original range, ignoring additional rows when present. The macro recorder code is as follows:

Sub TestSort()
'
' TestSort Macro
'
' Keyboard Shortcut: Ctrl+s
'
Range("E2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 1).Range("A1:A17155"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:R17156")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

It seems that the range for sorting should have a relative addressing format, but it just "hard codes" the range that I used when doing the original macro recording.

I'm sure that I am missing something basic here, but I am stuck.

ChartMaster

 

[IndirectCell]

Fine if you guys are gona be like that, heres the cleaned up version..

Sub sortfix()
    Dim Row, Column, Sorton, Sortdata
    
    Row = WorksheetFunction.CountA(Range("A:A")) ' row is now last row
    Column = WorksheetFunction.CountA(Range("1:1")) 'Column is now last column
    Sorton = "A1:" & Cells(Row, 1).Address(0, 0) ' example = A1:A100
    Sortdata = "A1:" & Cells(Row, Column).Address(0, 0) ' example = A1:Z100

    'all set now for some nifty sorting, in both rows and columns dynamicly.
    
    ' REMEMBER TO EDIT "SHEET1" TO WHATEVER YOU NAMED YOUR SHEETS

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
    Key:=Range(Sorton), Sorton:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal

    'A1:A100 now set as the row everything gets sorted by

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Sortdata) 'and this sets the whole datatable to sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'and now its sorted
End Sub

 

[snowbounduk]

Sub SortMyData()
' This is macro sorts a Dynamic data area that starts from Col A1 to any other Column.
' The Row & Column selection is Dynamic as the file could be 1000 rows today and 800
' rows tomorrow and 1200 the next day
' It sorts field in Column R and sorts in descending sequence
' Row 1 contains Headers and the data is in Sheet1 of the spreadsheet
'
'******************************* Define variables for the data that I want to store for later use
Dim MyDataFirstCell
Dim MyDataLastCell
Dim MySortCellStart
Dim MySortCellEnd

Windows("CustomerOrdersGreaterThanFC.xlsx").Activate ' Go to my data file

'************************** Establish the Data Area
Range("A1").Select 'Get to the first cell of data area
MyDataFirstCell = ActiveCell.Address 'Get the first cell address of Data Area
Selection.End(xlDown).Select 'Get to Bottom Row of the data
Selection.End(xlToRight).Select 'Get to the last Column and data cell by heading to the righthand end
MyDataLastCell = ActiveCell.Address 'Get the Cell address of the last cell of my data area

'************************** Establish the Sort column first and last data points.
Range("R2").Select 'Get to first cell of data sort Column (Example Col 'R' Row 2 becuase Row 1 contains the header)
MySortCellStart = ActiveCell.Address 'Get the Cell address of the first cell of my data sort Column
Selection.End(xlDown).Select 'Get to the bottom Row of data
MySortCellEnd = ActiveCell.Address 'Get the Cell address of the last cell of my sort Column



'************************** Start the sort by specifying sort area and columns
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
Key:=Range(MySortCellStart & ":" & MySortCellEnd), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(MyDataFirstCell & ":" & MyDataLastCell)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Works a treat, easy to modify for sort column, many thanks!

 

[CeeJay724]

Thank you so much PuffDaddy

Your post really saved me a few hours of trying to figure this out!

 

[css0911]

Hi..

I have defined few name ranges (Not dynamic) , column headers A1 to I1
I tried the code, it sorts only Column A , bit not B to I.

Assist pls

Thanks