# Sort data in multiple columns without a fixed range



## j4ttlife (Dec 20, 2022)

Hi, I'm trying to use a macro that sorts data in multiple columns. 

However, upon viewing the code after recording the macro, I notice a range has been declared (to row 483). 

Issue with the below code is the ranges only go to row 483. Next week it may be 500+ etc


*Original recorded Macro*


```
Cells.Select
    ActiveWorkbook.Worksheets("Oct data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("A2:A483"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("R2:R483"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Oct Data").Sort
        .SetRange Range("A1:AV483")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.Run "ConnectChartEvents"
    Sheets("Oct Data").Select
```

*My attempt below:*


```
Dim lastA As Long

    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("A1:A" & lastA), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
```


----------



## bferraz (Dec 20, 2022)

Hey, can you check if the code below works for you?


```
Dim lastRow As Long

With ActiveWorkbook.Worksheets("Oct data")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add2 Key:=.Range("R2:R" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With .Sort
        .SetRange .Range("A1:AV" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Application.Run "ConnectChartEvents"
Sheets("Oct Data").Select
```


----------



## Fluff (Dec 20, 2022)

How about
	
	
	
	
	
	



```
Dim UsdRws As Long
   
   With Worksheets("Oct data").Sort
      UsdRws = .Worksheet.Range("A" & Rows.Count).End(xlUp).Row
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
         DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("R1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
         DataOption:=xlSortNormal
      
      .SetRange Range("A1:AV" & UsdRws)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

    Application.Run "ConnectChartEvents"
    Sheets("Oct Data").Select
```


----------



## j4ttlife (Dec 20, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


Thanks Fluff. I was receiving an error 438 but slightly ameneded the UsdRws variable range to the below and it has done the trick. Appreciate the help.


```
UsdRws = Range("A" & Rows.Count).End(xlUp).Row
```


----------



## j4ttlife (Dec 20, 2022)

bferraz said:


> Hey, can you check if the code below works for you?
> 
> 
> ```
> ...


Thanks for the reply. I tried the other solution provided and have managed to resolve my issue, but I appreciate your help.


----------



## Fluff (Dec 20, 2022)

j4ttlife said:


> I was receiving an error 438


Oops that line should have been
	
	
	
	
	
	



```
UsdRws = .Parent.Range("A" & Rows.Count).End(xlUp).Row
```
Your change may give the wrong value if the Oct Data sheet is not active.


----------

