Custom sort dynamic range using VBA

Question

I receive an Excel file every morning which I like to sort into a more logical manner. All of the column headings are always the same, but the number of rows may change.

I'm trying to put together a macro that highlights the entire region (starting in B2). It needs to sort column C (ascending), G (descending), H (ascending) and I (descending).

I started off by using the macro recorder and am now trying to clean up the code it spat out.

So far I've managed to put together code that selects the region from B2 to the right and then down. Then when defining the sorting criteria for each column, I've tried to make sure that the range selected goes from the top of the list in row 3 (row 2 has headers, row 3 is first item in the list) and then dynamically selects down for each relevant column. However, after the With statement I'm struggling to get the range to be dynamic (it's just the macro-recorded static range still).

I'm also getting an 'Run-time error '1004': Application-defined or object-defined error' after .Apply.

Sub Macro1()
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G3", 
Range("G3").End(xlToRight)) _
    , SortOn:=xlSortOnValues, Order:=xlDescending, 
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C3", 
Range("C3").End(xlToRight)) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H3", 
Range("H3").End(xlToRight)) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I3", 
Range("I3").End(xlToRight)) _
    , SortOn:=xlSortOnValues, Order:=xlDescending, 
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("B2:Q31") ' NOT SURE HOW TO MAKE DYNAMIC HERE
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply ' GETTING ERROR HERE
End With
End Sub

I recommend reading this [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — erazorv4, Jun 06 '19 at 13:42

Mikku · Accepted Answer · 2019-06-06T13:44:25.763

0

Just had to change the Range to Selection as you already have dynamically selected the Range in work:

Sub Macro1()
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("G3", Range("G3").End(xlDown)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C3", _
Range("C3").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("H3", _
Range("H3").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("I3", _
Range("I3").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'

With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Selection.Address)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

End Sub

See if it works now.

edited Jun 06 '19 at 13:44

answered Jun 06 '19 at 13:21

Mikku

6,538
3
15
38

@bennyjim .. Let us know if you still get any error. – Mikku Jun 06 '19 at 13:25
Thanks for checking. Is the formatting ok for `SetRange Selection`? Don't I need a period after 'SetRange'? Also, I'm still getting the error on the ` Apply` statement unfortunately - not sure how to fix that..? – bennyjim Jun 06 '19 at 13:25
What is the error you are getting ? Also can you paste sample data to replicate the problem ? – Mikku Jun 06 '19 at 13:29
'Run-time error '1004': Application-defined or object-defined error' when the code gets to ` .Apply`. As for data, it's just a normal table of data. – bennyjim Jun 06 '19 at 13:37
@bennyjim ... You were using wrong Dynamic Ranges in the Keys Instead of `End(xlToRight)` you have to use `End(xlDown)` ... I have amended the answer. It should work now – Mikku Jun 06 '19 at 13:45
Sorry, so stupid of me!!! I tried your code, it works except for you've put a '2' after the `Add` methods - I deleted those and it's fixed. Many thanks for your help. – bennyjim Jun 06 '19 at 13:52

Custom sort dynamic range using VBA

1 Answers1