VBA Run time error 1004 Sort Method of Range class failed-CodePudding

I recently started working for a company that recently acquired another company that uses VBA and excel macros. I have next to no experience in these things but I've been tasked with fixing errors and while I've been able to debug some issues this one has got me stumped.

Here is the code that's causing the error:

lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row

WorkRange = "A1:BB" & lastrow

Range(WorkRange).Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

Any help is appreciated and I'm happy to provide any other information that might be needed.

CodePudding user response：

Sort a (Table) Range

Option Explicit

Sub SortRange()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' If this worksheet is in the workbook containing this code,
    ' then in the VBE Project Explorer, you can identify its names,
    ' e.g. 'Sheet1 (Data)' where 'Data' is its tab name while 'Sheet1'
    ' is its code name. Then you can more safely reference the worksheet
    ' with either 'Set ws = ThisWorkbook.Worksheets("Data")'
    ' or use the safest option by removing the previous line and instead of 'ws'
    ' just using 'Sheet1' in the continuation of the code. In the latter
    ' case, the code will still work correctly if someone decides that
    ' 'Calculation' is a better (tab) name then 'Data'.

    ' Calculate the last row ('LastRow').
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Reference the range ('rg').
    Dim rg As Range: Set rg = ws.Range("A1:BB" & LastRow)
    ' Sort the range.
    rg.Sort rg.Columns(3), xlAscending, , , , , , xlYes

    ' If you have nice table data i.e. starting in cell 'A1', one row of headers
    ' and no empty rows or columns, you can shorten the code by using...
    'Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    'rg.Sort rg.Columns(3), xlAscending, , , , , , xlYes
    ' ... when the last row is not necessary.
    
    ' The last option, when using the code name e.g. 'Sheet1',
    ' would require only...
    'Dim rg As Range: Set rg = Sheet1.Range("A1").CurrentRegion
    'rg.Sort rg.Columns(3), xlAscending, , , , , , xlYes
    ' ... without the worksheet declaration.

End Sub