Macro to sort worksheet based on last data row and column

[khorton08]

Hi,

I am working on a macro in which constantly gets updated with new columns each quarter and new rows each quarter. To avoid having to update the macro every few months to reflect the new rows and columns, I would like to edit the sort button to go to the end of the columns (all have headers) and to the bottom of row of data (sort criteria always has data in cell). My current code is below. Can anyone help?


Range F = The column I need to sort the worksheet by in ascending order. 5000 is just a estimated number to hold us over until I can get the correct coding to work.
Range A - BL = Current start and finish of columns. But a few columns have been or will be added shortly.




ActiveWorkbook.Worksheets("Master List").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master List").Sort.SortFields.Add Key:=Range("F4:F5000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Master List").Sort
.SetRange Range("A3:BL5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub


I appreciate anyone who can give me tips.

 

[Kamolga]

I assume not blank from A1 to the right, then down to reach last column last cell, otherwise change A1 into...

ActiveWorkbook.Worksheets("Master List").Sort.SortFields.Clear

 Dim Lastrow As Long
 Dim lastcolumn As Long


    Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Selection.Activate

    Lastrow = ActiveCell.Row
    lastcolumn = ActiveCell.Column

    ActiveWorkbook.Worksheets("Master List").Sort.SortFields.Add Key:=Range(cells(4,6),cells(lastrow,6)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master List").Sort
        .SetRange Range(Cells(3,1),cells(lastrow,lastcolumn))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
 End Sub
<strike></strike>

I believe we can also change Range("F4:F5000") into range("F4:F"&lastrow) if you had errors when running script