How to select a range of data when the range is variable?

Question

How to select a range of data when the range is variable?

Gaspapa 6

I wrote the following code to select a variable range but the range selected is fixed. :

Windows("AllTransactions.xlsm").Activate

Range("A1", Range("F1").End(xlDown)).Select

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

ActiveWorkbook.Worksheets("AllTransactions").Sort.SortFields.Add2 Key:=Range( _

    "B2:B220"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

    xlSortNormal

With ActiveWorkbook.Worksheets("AllTransactions").Sort

    .SetRange Range("A1:F220")

    .Header = xlYes

    .MatchCase = False

    .Orientation = xlTopToBottom

    .SortMethod = xlPinYin

    .Apply

End With
``` The problem is that the Key:= a finite range "B2:B220", not the variable range. How do I correct this?

0 comments

2 answers

Your answer

Answer 1

You can use Range("F1").End(xlDown).Row to determine the last populated row dynamically, then use that row number to define both the sort key and the sort range. The following code assumes row 1 contains headers and column F is populated continuously.

Windows("AllTransactions.xlsm").Activate

Dim lastRow As Long
lastRow = Range("F1").End(xlDown).Row

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

ActiveWorkbook.Worksheets("AllTransactions").Sort.SortFields.Add2 _
    Key:=Range("B2:B" & lastRow), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    DataOption:=xlSortNormal

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

The key change is Key:=Range("B2:B" & lastRow). The & operator concatenates the column reference with the dynamically calculated last row. The .SetRange statement also uses lastRow, so the entire dataset is sorted rather than a fixed range.

Note: Range("F1").End(xlDown).Row can stop at the first blank cell in column F. A more reliable approach, especially if column F may contain gaps, is:

lastRow = Cells(Rows.Count, "F").End(xlUp).Row

This finds the last populated cell in column F by searching upward from the bottom of the worksheet.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Calculate the last populated row once, then use that value in both the sort key and the complete sort range.

Sub SortTransactions()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long

    Set wb = Workbooks("AllTransactions.xlsm")
    Set ws = wb.Worksheets("AllTransactions")

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:F" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

lastRow is found by starting at the bottom of column B and moving upward to the final populated cell. Range.End behaves like pressing End+Up Arrow in Excel.^1^

The two dynamic ranges serve different purposes:

B2:B<lastRow> is the sort key—the values determining row order. The Key parameter accepts a Range.^2^
A1:F<lastRow> is the complete range whose rows are rearranged. SetRange defines the worksheet range over which the sort operates.^3^

All Range, Cells, and Rows references are qualified with ws, preventing Excel from accidentally using whichever worksheet happens to be active. Consequently, Activate and Select are unnecessary; VBA can operate directly on a worksheet’s ranges.^4^

If column B can contain blanks near the bottom while other columns still contain data, determine lastRow from a column that is populated on every transaction row, such as column A, and keep column B as the sort key:

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

References