Sorting an Excel Sheet when First Row, Last Row, First Column, Last Column saved only variables

Question

I've found a few articles that I thought would help me sort my sheet via VBA (like VBA Excel sort range by specific column), but I just can't get them to work.

When I'm sorting, I have a number of worksheets open, which I think may be complicating the issue. But when I set the ranges I'm using the sort, I select the worksheet, so I just don't know.

If you look at the code, you can see that I have lots of variables defined that might be useful, so feel free to use any of those that would be helpful.

'These are created elsewhere as Public, or passed into Sub
Dim SourceSheet As Worksheet
Dim MyWB As String
Dim MyWS As String
Dim ColNum As Integer
Dim SourceFirstRow As Integer
Dim SourceLastRow As Integer
Dim FinalFirstRow As Integer
Dim FinalFirstColumn As Integer
Dim FinalLastColumn As Integer

'These are created and used in the subroutine
Dim FinalSheet As Worksheet
Dim AllCells As Range
Dim SortColumn As Range
Dim SourceRow As Integer
Dim CurrentFinalRow As Integer

'These are actually set elsewhere and passed in, shown here for example
Set SourceSheet = ThisWorkbook.Worksheets(1)
Set SourceFirstRow = 2
Set SourceLastRow = 15
Set MyWB = "DataFile"
Set MyWS = "Data"
Set FinalFirstRow = 2
Set FinalFirstColumn = 1
Set FinalLastColumn = 20
Set ColNum = 4

'These is the relevant code from the subroutine
Set FinalSheet = Workbooks(MyWB).Worksheets(MyWS)
Set CurrentFinalRow = FinalFirstRow
Set AllCells = FinalSheet.Cells
Set SortColumn = FinalSheet.Columns(ColNum)

For SourceRow = SourceFirstRow To SourceLastRow
    'Fill in a whole bunch of data into the rows and columns on the final sheet
    'not all sourcerows are transferred
    CurrentFinalRow = CurrentFinalRow + 1 'Sets it ready for the next blank line to be filled
Next SourceRow

Range(AllCells & CurrentFinalRow - 1).Sort key1:=Range(SortColumn & CurrentFinalRow - 1), order1:=xlAscending, Header:=xlYes

`Range(AllCells & CurrentFinalRow - 1)` looks a bit off. What are you trying to do here? If you're passing a row number, then you want a column letter before it. `AllCells` is a `Range`. — BigBen, Jun 09 '21 at 18:46
I was using code from Dmitry Pavliv in the post I linked to. He used lastrow as well as a range, so I thought you needed the "bottom" of the range defined. Is that not the case? — dhunton, Jun 09 '21 at 18:57
Range(AllCells).Sort key1:=Range(SortColumn), order1:=xlAscending, Header:=xlYes doesn't work either. :( — dhunton, Jun 09 '21 at 19:04
It might be useful to read up on how to use [`Range`](https://learn.microsoft.com/en-us/office/vba/api/excel.range(object)) and its parameters. — BigBen, Jun 09 '21 at 19:06
Got a good reference on it? I've looked at 5 different sites about Range and can't make sense of them. — dhunton, Jun 09 '21 at 19:17

score 0 · Answer 1 · answered Jun 09 '21 at 20:23

So, with further research and the code posted above, the answer turns out to be:

Range(Cells(FinalFirstRow,FinalFirstColumn), Cells(CurrentFinalRow-1,FinalLastColumn)).Sort _ key1:=Range(Cells(FinalFirstRow,ColNum),Cells(FinalFirstRow,ColNum)), _ order1:=xlAscending, Header:=xlNo

score 0 · Answer 2 · answered Jun 10 '21 at 00:51

Sorting a Range

' Declare all whole numbers as 'Long', no need to use 'Integer'.
' The 'Set' keyword is used for objects like workbooks, worksheets
' and ranges. Remove it from the other variable types.
' Set or add values to the variables close to their declarations.
' Qualify your objects: 'FinalSheet.Range(...)' vs 'Range(...)'.
' Use variables to make the code more readable ('drg').

'These are actually set elsewhere and passed in, shown here for example
Dim SourceSheet As Worksheet: Set SourceSheet = ThisWorkbook.Worksheets(1)
Dim SourceFirstRow As Long: SourceFirstRow = 2
Dim SourceLastRow As Long: SourceLastRow = 15
Dim MyWB As String: MyWB = "DataFile.xlsx" ' or whatever extension
Dim MyWS As String: MyWS = "Data"
Dim FinalFirstRow As Long: FinalFirstRow = 2
Dim FinalFirstColumn As Long: FinalFirstColumn = 1
Dim FinalLastColumn As Long: FinalLastColumn = 20
Dim ColNum As Long: ColNum = 4

'These is the relevant code from the subroutine
Dim FinalSheet As Worksheet
Set FinalSheet = Workbooks(MyWB).Worksheets(MyWS)
Dim CurrentFinalRow As Long: CurrentFinalRow = FinalFirstRow
Dim AllCells As Range: Set AllCells = FinalSheet.Cells ' useless
Dim SortColumn As Range: Set SortColumn = FinalSheet.Columns(ColNum) ' useless

Dim SourceRow As Long
For SourceRow = SourceFirstRow To SourceLastRow
    'Fill in a whole bunch of data into the rows and columns on the final sheet
    'not all source rows are transferred
    CurrentFinalRow = CurrentFinalRow + 1 'Sets it ready for the next blank line to be filled
Next SourceRow

Dim drg As Range
With FinalSheet
    Set drg = .Range(.Cells(FinalFirstRow, FinalFirstColumn), _
        .Cells(CurrentFinalRow - 1, FinalLastColumn))
End With

drg.Sort Key1:=drg.Cells(ColNum), Order1:=xlAscending, Header:=xlNo

Sorting an Excel Sheet when First Row, Last Row, First Column, Last Column saved only variables

2 Answers2

Sorting a Range