Sort column with empty spaces

Question

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

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Find last used row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) — Pᴇʜ, May 03 '22 at 14:39
use the initial code with line `.SetRange Range("E1:E" & LastRow)` there are multiple methods to find `LastRow` number, plenty of examples online and in this web — NoobVB, May 03 '22 at 14:43

VBasic2008 · Accepted Answer · 2022-05-04T10:24:34.113

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

Edit

Sub SortColumnVariables()
    
    Dim crg As Range ' Whole Column; `E1:E1048576`
    Set crg = ThisWorkbook.Worksheets("Sheet1").Columns("E")
    
    Dim lCell As Range ' Last Cell
    ' Starting from the last column cell ('After:=crg.Cells(1)' i.e. 'E1048576'),
    ' find the first non-empty ('LookIn:=xlFormulas') cell looking
    ' upwards ('SearchDirection:=xlPrevious').
    Set lCell = crg.Find("*", crg.Cells(1), xlFormulas, , , xlPrevious)
    ' You can also use the argument names when the order is not important:
    'Set lCell = crg.Find(What:="*", After:=crg.Cells(1), _
        LookIn:=xlFormulas, SearchDirection:=xlPrevious)
    
    If lCell Is Nothing Then Exit Sub ' empty column; no non-empty cell found
        
    ' Reference the range from the first ('E1')
    ' to the last (non-empty) cell ('lCell').
    Set crg = crg.Resize(lCell.Row)
    ' Or more obviously:
    'Set crg = ThisWorkbook.Worksheets("Sheet1").Range(crg.Cells(1), lCell)
        
        
    ' Try this:
    ' Put the cursor to the left of a comma, delete the comma and add it again.
    ' Now you will see the intelli-sense showing you loads of 'Sort' parameters.
    ' i.e. `xlNo` refers to the headers.
    crg.Sort crg, xlAscending, , , , , , xlNo
    ' You can also use the argument names when the order is not important:
    'crg.Sort Key1:=crg, Order1:=xlAscending, Header:=xlNo

End Sub

Thank you so much! I was wondering how your code works? I'm assuming lCell means last cell and the variable is the range down to the last cell in that column? Set is to find the last cell. Would this be correct? I don't quite understand the if function as it seems to say to stop the sub if there are no more cells. Why are there so many commas after xlAscending? Thanks! — Bikat Uprety, May 04 '22 at 07:50

Sort column with empty spaces

1 Answers1

Sort a Column