Sort Range Using VBA And Ignoring Or Discarding Blank Rows

[Admin]

Hi

try this one. adjust the ranges accordingly.

Option Explicit
Sub Sort1()

    Dim NonBlankData(), n As Long, r As Long, c As Long
    Dim WholeData
    
    WholeData = Range("R3:AC17").Value2
    
    ReDim NonBlankData(1 To UBound(WholeData, 1), 1 To UBound(WholeData, 2))
    
    For r = 1 To UBound(WholeData, 1)
        '//check whether the first column has data. if so, proceed. Replace the 1 with appropriate column to check
        If Len(WholeData(r, 1)) Then
            n = n + 1
            For c = 1 To UBound(WholeData, 2)
                NonBlankData(n, c) = WholeData(r, c)
            Next
        End If
    Next
    
    If n Then
        With Range("AE3")
            .Resize(n, UBound(NonBlankData, 2)).Value2 = NonBlankData
            With .Resize(n, UBound(NonBlankData, 2))
                .sort Key1:=.Cells(1, 1), Order1:=xlAscending, Key2:=.Cells(1, 8), _
                Order2:=xlDescending, Key3:=.Cells(1, 9), Order3:=xlAscending, _
                Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
                xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal
            End With
        End With
    End If
    
End Sub