Selecting all data on a Worksheet

[DctheDC]

I have recorded a macro to sort my worksheet in a specific order and I get the following VBA.

Cells.Select
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
"A2:A10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
"G2:G10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
"L2:L10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DReg").Sort
.SetRange Range("A1:AB10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub



If I add another row to the sheet beyond row 10 or another column beyond AB then the new data is not picked up by the sort macro. Is there a way to select the whole sheet to be covered by the sort?

Cheers

 

[JackDanIce]

Try:

Sub SortMe()

    Dim x       As Long
    Dim y       As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("DReg")
        x = .Cells(.Rows.count, 1).End(xlUp).row
        y = .Cells(1, .Columns.count).End(xlToLeft).column
        
        .Sort.SortFields.Clear
        With .Sort
            .SortFields.add key:=Parent.Cells(1, 1).Resize(x), Order:=xlAscending
            .SortFields.add key:=Parent.Cells(1, 7).Resize(x), Order:=xlAscending
            .SortFields.add key:=Parent.Cells(1, 12).Resize(x), Order:=xlAscending
        End With
        
        With .Sort
            .SetRange Parent.Cells(1, 1).Resize(x, y)
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
        .Select
    End With
    
    With Application
        .Goto Cells(1, 1)
        .ScreenUpdating = True
    End With
    
End Sub

 

[tonyyy]

DctheDC,

Although untested, I'm certain @JackDanIce's routine will be far superior to any recorded macro. If, however, you expect to record macros in the future and want to adapt them to a dynamic/changing range, you might consider the following...

Sub test()
Dim LastRow As Long, LastColumn As Long
LastRow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
LastColumn = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    
Cells.Select
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
    [COLOR=#ff0000]"A2:A" & LastRow[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
    [COLOR=#ff0000]"G2:G" & LastRow[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("DReg").Sort.SortFields.Add Key:=Range( _
    [COLOR=#ff0000]"L2:L" & LastRow[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("DReg").Sort
    .SetRange [COLOR=#ff0000]Range(Cells(1, 1), Cells(LastRow, LastColumn)[/COLOR])
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A1").Select
End Sub

In the recorded macro, the last row is hard coded, eg, "A2:A10" or "G2:G10". So this macro defines a variable named LastRow and determines its value by using the .Find method to search every cell for any value, and the last cell with a value is in the last row. So now it's just a matter of replacing Range("A2:A10") with Range*"A2:A" & LastRow).

It's a similar process for the Last Column.

Cheers,

tonyyy