VBA code to sort a variable length range

ChartMaster

New Member
Joined
May 22, 2009
Messages
4
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Using a dynamic range name as the address might do the trick:
http://www.contextures.com/xlNames01.html


-----------------------
Note: This typically works best when the data in the dynamic range is "packed" - no blanks anywhere. To refer to it in code you can probably just record again, but also, if A1:A10 is named MyRange, these are equivalent:

Range("A1:A10")
Range("MyRange")
 
Upvote 0
I did try using a named range in the macro, but it didn't work. I will try using the naming formula that you suggest to see if that works. I am assuming that I would set up that range name outside the macro and just refer to the range name in the sort commands, is that correct?

ChartMaster
 
Upvote 0
Yes. Define the named range first, then use it in your macro. Let us know how you come along here.

Alex
 
Upvote 0
I tried the suggestion, but still have the same problem of getting the macro to use the dynamic range definition as the range to sort. It still uses the range from the first sort that was done. I tried entering the dynamic range name into the vba sort routine as follows, but it wouldn't accept it and stopped with an error ("AddrRange" is the name I assigned to the dynamic range):


Application.Goto Reference:="AddrRange"
ActiveWorkbook.Worksheets("Std Addresses").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Std Addresses").Sort.SortFields.Add Key:=Range( _
"AddrRange"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Std Addresses").Sort
.SetRange Range("AddrRange")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Any other thoughts?
 
Upvote 0
Heres a snippet of code that I use to sort a variable row range (Excel 2003)

NumberOfRows = Sheets("sheet3").Range("m65536").End(xlUp).Row


Range(Cells(2, 13), Cells(NumberOfRows, 16)).Sort Key1:=Range("P9"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Maybe something like this will help?
 
Last edited:
Upvote 0
nigel's code looks good too. If you like, give it a go. If interested in pursuing dynamic range names a little more, re-record your code and post it here. Also post the results of this code:

Code:
Sub foo()
MsgBox Worksheets("Std Addresses").Range("AddrRange").Address
End Sub

(Note: When reporting errors, always (if possible) give the error number, error description, and the line of code the error occurs on.)
 
Upvote 0
nigel's code looks good too. If you like, give it a go. If interested in pursuing dynamic range names a little more, re-record your code and post it here. Also post the results of this code:

Code:
Sub foo()
MsgBox Worksheets("Std Addresses").Range("AddrRange").Address
End Sub

(Note: When reporting errors, always (if possible) give the error number, error description, and the line of code the error occurs on.)
Thanks to both of you for your help. I think I have something now that is working. I haven't completed the procedure yet, but it looks like it will work. Thanks!
 
Upvote 0
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
 
Upvote 0
here you guys go :x

Code:
Function LastRow()
    Dim rowfind As Long
    rowfind= WorksheetFunction.CountA(Range("A:A"))
    LastRow = rowfind
End Function

Function LastColumn()
    Dim columnfind As Long
    columnfind = WorksheetFunction.CountA(Range("1:1"))
    LastColumn = columnfind
End Function

Sub sortyness()
Dim Sorton, sortdata

    Sorton = "A1:" & Cells(LastRow, 2).Address(0, 0) 'this one be like A1 to A something last

    sortdata = "A1:" & Cells(LastRow, LastColumn)Address(0, 0) 'this one be like A1 to last row and column
    
    ActiveWorkbook.Worksheets("Tabell").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tabell").Sort.SortFields.Add _
    Key:=Range(Sorton), Sorton:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Tabell").Sort
        .SetRange Range(sortdata)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I had to rewrite for forum it seeing i use a different language but i think the people searching for a formula like this will understand it.

Sorton is basicly what column your sorting by, and sortdata is the whole thing whit headers.

You dont have to use those functions alone, you could just put them inside the sorting formula unless your gona use them elsewhere.. i keep them split cus theyr usefull for other stuff.. like filling comboboxes and listboxes :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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