Excel VBA Sort Range with Blank AND Empty Values at Bottom of Range

Question

I am sorting a very large range in Excel, which consists of multiple columns, not all of which are filled. In some of the columns, there are times where white space is entered and times where the cell is Empty. What I'd like to do is have both white space and empty cells moved to the bottom of the range. Currently, excel puts white space values at the top and empty cells at the bottom. Alternatively, I wouldn't mind a sanitizing function that could pass over the range and replace white space values with Empty ones. I wrote one, but it is incredibly slow for the range I'm given (nearly 4000 rows, and it takes 2+ minutes to complete). As well, I posted the sorting sub I'm using.

I've looked into custom order, but it looks like it requires you to specify all values you plan on using. Is it possible to use a custom comparison function, without writing a whole new sort sub?

Sub SortCallLog()
    Application.ScreenUpdating = False
    Dim longTime As Double
    longTime = Millis
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim sortOrder As Variant
    Set ws = Worksheets("CallLog")
    Call addToIndexesHV(FirstCBSort, SecondCBSort, ThirdCBSort, FourthCBSort) ' These are global variables referring to the sort order
    sortOrder = getSortOrder(False)
    lastRow = GetLastRow(ws)
    With ws.Sort
        .SortFields.Clear
        For sortIndex = 0 To getVariantLength(sortOrder) - 1
            .SortFields.Add Key:=Range(ConvertCBSortToColRow(CStr(sortOrder(sortIndex)))), Order:=xlAscending, SortOn:=xlSortOnValues, DataOption:=xlSortNormal
        Next sortIndex
        .Header = xlYes
        .MatchCase = False
        .SetRange ws.Range("B1:X" & (lastRow))
        .Apply
    End With
    Debug.Print (Millis - longTime)
    Application.ScreenUpdating = True
Exit Sub

Sanitizing section

For Each cell In ws.Range("B2:L" & (lastRow)).Rows.Cells
    cell.value = Trim(cell.value)
    If "" = cell.value Then
        cell.value = Empty
    End If
Next cell

cyboashu cyboashu · Accepted Answer · 2016-08-12T19:47:48

Instead of for each cell use array to sanitize your cells

Replace sheet1.usedrange with your actual range. It will take 1 or 2 seconds

Sub test()
    Dim arr
    Dim lctrRow As Long
    Dim lctrCol As Long

    arr = Sheet1.UsedRange

    For lctrRow = LBound(arr, 1) To UBound(arr, 1)
        For lctrCol = LBound(arr, 2) To UBound(arr, 2)
            If Trim(arr(lctrRow, lctrCol)) = "" Then
               arr(lctrRow, lctrCol) = Empty
            End If
        Next
    Next

    Sheet1.UsedRange.Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

This will reduce over all time. Also make sure your calculation mode is manaul when you start sorting/sanitizing. That might help too.

Excel VBA Sort Range with Blank AND Empty Values at Bottom of Range

1 Answers