Need VBA to go to bottom of a column of data then down one cell then delete all rows below that and then resort the worksheet using column A

[Maple]

Hello Excel Team

I need the VBA code to go down to the last cell of a continuous column of data (Say Column L) then move down one cell and then delete all the rows from that cell downward and finally doing a sort on the entire worksheet using column A. I have been trying to learn from videos but they always seem to be solving very unique problems.

I hope you can help

Thanks

Maple

 

[Jamie McMillan]

Hello Maple,

You will need to edit the below code to fit the range of your worksheet.

Sub ClearAndSort()
    Application.ScreenUpdating = False

    ActiveCell.End(xlDown).Offset(1).Select 'Selects the bottom row of the ACTIVECELL + 1
    Cells(ActiveCell.Row, 1).Select 'Selects Column 1 of that row

    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 6)).ClearContents 'Selects the range to delete
    'Leave th (0, 0) and change the (1, 6) to fit currently it selects 2 rows and 7 columns (Always + 1 to the number in brackets)
    
    
    Range("A1:G14").Select 'Selects the range to be sorted
    ActiveWorkbook.Worksheets("Sheet16").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet16").Sort.SortFields.Add Key:=Range("A1:A14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet16").Sort
        .SetRange Range("A1:G14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

If you want to see the range you are selecting to make sure it is right; use this in a separate macro

Sub Select()
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 6)).Select
End Sub

Now you select any any cell in the column with the data and run the macro

Jamie

 

[Peter_SSs]

You could try this. I have assumed headers in row 1. If that is not the case, change the xlYes to xlNo

Sub Test()
  Rows(Range("L1").End(xlDown).Row + 1 & ":" & Rows.Count).Delete
  ActiveSheet.UsedRange.Sort Key1:=Columns("A"), Order1:=xlAscending, Header:=xlYes
End Sub

 

[Maple]

Thank you both!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.