Dynamic Range in Macro

[Zaigham]

Hi,
I have recorded a macro in excel to sort data in a table. The rows of data vary each time. Sometimes it is less or sometimes it is more. Below is the part of code in which the range has been hard-coded. Please guide me to a way to get rid of this "Range("B4:L1098").Select" to be adjusted according to data volume.
"Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B4:L1098").Select
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"L5:L1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"D5:D1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CurntPenExp").Sort
.SetRange Range("B4:L1098)

Regards
Zaigham

 

[Herakles]

One of the good things about tables is that you don't need to worry about the number of rows.

You just refer to the worksheet name (SortTable), table name (SortTable1), columns headers ([Joining Date] and [Salary]) and
whether ascending or descending for each column.

Can you apply this idea to your project?

Worksheets("SortTable").Range("SortTable1").Sort _
        Key1:=Range("SortTable1[Joining Date]"), Order1:=xlAscending, _
        Key2:=Range("SortTable1[Salary]"), Order2:=xlDescending, Header:=xlYes

 

[Zaigham]

One of the good things about tables is that you don't need to worry about the number of rows.

You just refer to the worksheet name (SortTable), table name (SortTable1), columns headers ([Joining Date] and [Salary]) and
whether ascending or descending for each column.

Can you apply this idea to your project?

Worksheets("SortTable").Range("SortTable1").Sort _
        Key1:=Range("SortTable1[Joining Date]"), Order1:=xlAscending, _
        Key2:=Range("SortTable1[Salary]"), Order2:=xlDescending, Header:=xlYes

Thanks for your response but the table in question is an ordinary table not the excel table which is created by Ctrl+T.

 

[rlv01]

If it is an actual table (ListObject) that you are sorting then try @Herakles suggestion. If it is a range of cells you are calling a table, here's one way. Note that I am making three assumptions 1) Data begins at B4 , columns are B to L, and row B is a header row.

    Dim SortRange As Range, Key1Range As Range, Key2Range As Range
    With ActiveWorkbook.Worksheets("CurntPenExp")
        Set SortRange = .Range("B4", .Range("L" & .Rows.Count).End(xlUp))    'B4 to Ln, where n = last row
        Set Key1Range = .Range("L4") '1st cell in column
        Set Key2Range = .Range("D4") '1st cell in column
        
        With .Sort
            .SortFields.Clear
            .Header = xlYes
            .SortFields.Add Key:=Key1Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=Key2Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange SortRange
            .Apply
        End With
    End With

 

[rlv01]

One more thing: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.

 

[Zaigham]

VBA Code:

    Dim SortRange As Range, Key1Range As Range, Key2Range As Range
    With ActiveWorkbook.Worksheets("CurntPenExp")
        Set SortRange = .Range("B4", .Range("L" & .Rows.Count).End(xlUp))    'B4 to Ln, where n = last row
        Set Key1Range = .Range("L4") '1st cell in column
        Set Key2Range = .Range("D4") '1st cell in column
       
        With .Sort
            .SortFields.Clear
            .Header = xlYes
            .SortFields.Add Key:=Key1Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=Key2Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange SortRange
            .Apply
        End With
    End With

Thank you very much Mr., this code worked fine for me. Also thankful to guide me regarding posting VBA Code. God bless you.