Sort column with empty spaces-CodePudding

I'm trying to sort a column in excel that has spaces between the various data entries.

Context: Originally, I had a macro that deleted the spaces between the rows, however, after asking on the community on why the macro was taking so long to run, I was told that the macro was essentially deleting every bank cell in the entire column.

So I created the following code to sort the data (the order in which the numbers appear doesn't matter).

While the code below works well, I'm trying a way to write it so that it doesn't reference a fixed amount of cells as the list of cells will vary.

Original Code

      Columns("E:E").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "E1:E3121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("E1:E3121")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

So I wrote the code below in order that it would detect all cells automatically, using current region, however, because CurrentRegion only finds cells adjacent to each other, and in my column there are thousands of cells that are seperated by one of more empty cells, the code below only sorts the first adjacent block of cells.

New Code

      Columns("E:E").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("E1").CurrentRegion, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("E1").CurrentRegion
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

Is there a way that the code above can be written so that it detects the last cell in a column and sorts it?

Thanks

CodePudding user response：

Sort a Column

Sub SortColumn()
    With ThisWorkbook.Worksheets("Sheet1").Columns("E")
        Dim lCell As Range
        Set lCell = .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' empty column
        With .Resize(lCell.Row)
            .Sort .Cells, xlAscending, , , , , , xlNo
        End With
    End With
End Sub