Syntax using variables in range address

[Yamezz]

I'm familiar with syntax such as the following, using one variable in the address:

Range("A4", "E" & LastRow)

But I can't work out the correct syntax for something using two variables, like:

ActiveSheet.Range("A3", LastColumn & LastRow).Select

Can someone point me to the correct syntax please?

 

[Michael M]

try

ActiveSheet.Range(Cells(3, 1), Cells(LastColumn, LastRow)).Select

 

[Michael M]

OOps...wrong way round
AND why are you selecting them, probably no need to ??

ActiveSheet.Range(Cells(3, 1), Cells(LastRow, LastColumn)).Select

 

[Yamezz]

Thanks Michael. The next step in the code is to sort the data according to the date in column B. For that, I'm using code generated by the macro recorder, which starts with a selection. I'm by no means proficient in VBA - I just cobble together bits of code I come across to grope my way to a solution, ha!

 

[Michael M]

If the code created by the recorder works, post it here and someone may be able to shorten it for you !

 

[Yamezz]

I did a bit of critical analysis on the code and saw where there was some macro-recorder-generated redundancy. My full code, which works is now:

Sub DateSorting()

Dim LastRow As Long, LastColumn As Long

LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.Cells(LastRow, Columns.Count).End(xlToLeft).Column

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.Sort
        .SetRange Range(Cells(3, 1), Cells(LastRow, LastColumn))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[Michael M]

that looks OK...and will allow for dynamic ranges of cells