Help With Part Of Current Code

[Dazzawm]

What do I need to change on the recorded code so it looks at the whole sheet rather than the range (AD2:AD216892, A2:A216892, A1:AX216892), as I will use on multiple files with different amount of rows/columns please?

Cells.Select
    Range("A1").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("AD2:AD216892"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range( _
        "A2:A216892"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AX216892")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Anfinsen]

Cells.Select
    Range("A1").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("AD2:AD216892"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range( _
        "A2:A216892"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange .Parent.UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

just changed .SetRange .Parent.UsedRange

 

[Dazzawm]

Cells.Select
    Range("A1").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("AD2:AD216892"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range( _
        "A2:A216892"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange .Parent.UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

just changed .SetRange .Parent.UsedRange

But it still has the same range, I may have files that have more than 216892 rows?

 

[Joe4]

But it still has the same range, I may have files that have more than 216892 rows?

Is all your data contiguous (no gaps of completely blank lines or columns in your data)?

 

[Dazzawm]

Is all your data contiguous (no gaps of completely blank lines or columns in your data)?

No, I think! All columns have a header in row one and stretch over to AY.

 

[Joe4]

No, I think! All columns have a header in row one and stretch over to AY.

OK, that confirms that there are no totally blank columns, but really does not address rows.
If you look at your data, from your first data row to your last data row, does every row in between have a values in at least ONE column?
Another way of asking it is this: do you have any COMPLETELY blank rows of data in the middle of your data (meaning every column in that row is completely blank)?

 

[Joe4]

Also, will you ALWAYS be sorting by column AD?

 

[Joe4]

Assuming my assumption are correct, I think this should do what you want:

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
   
'   Get entire range to sort
    Set rng1 = Range("A1").CurrentRegion
   
'   Set range AD, and exclude first row
    Set rng2 = Intersect(Range("AD:AD"), rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, rng1.Columns.Count))
   
'   Set range AD, and exclude first row
    Set rng3 = Intersect(Range("A:A"), rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, rng1.Columns.Count))

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(rng2, _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=rng3, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng1
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Dazzawm]

Also, will you ALWAYS be sorting by column AD?

Yes and no blank rows.

 

[Dazzawm]

Assuming my assumption are correct, I think this should do what you want:

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
  
'   Get entire range to sort
    Set rng1 = Range("A1").CurrentRegion
  
'   Set range AD, and exclude first row
    Set rng2 = Intersect(Range("AD:AD"), rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, rng1.Columns.Count))
  
'   Set range AD, and exclude first row
    Set rng3 = Intersect(Range("A:A"), rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, rng1.Columns.Count))

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(rng2, _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=rng3, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng1
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

So I just replace what I posted with this?