Sort Range Using VBA And Ignoring Or Discarding Blank Rows

**analyst** · 02-05-2014, 12:31 PM

I've following code

Code:

Range("R3:AC17").Copy
Range("AE3").Select
Selection.PasteSpecial Paste:=xlPasteValues

    Selection.Sort Key1:=Range("AE3"), Order1:=xlAscending, Key2:=Range( _
        "AL3"), Order2:=xlDescending, Key3:=Range("AM3"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal

**Admin** · 02-05-2014, 01:20 PM

Hi

Put some fictitious numbers in the blank cells before sorting and replace it after sorting.

**analyst** · 02-05-2014, 01:44 PM

I've 20- 25 such ranges on same sheet, which I need to do at one shot, changing manually, and replacing would be time consuming

Here is some solution, but I'm not able to change it to my convenience.
http://www.mrexcel.com/forum/excel-q...ting-data.html

**Admin** · 02-05-2014, 02:02 PM

Hi

You can do this in the code itself.

Code:

Sub kTest()
    
    Dim r As Range
    
    Set r = Range("a1:k100")
    
    Const fNum = -9999999.99
    
    With r
        On Error Resume Next
        .SpecialCells(4).Value = fNum
        'your code for sort
        .Replace fNum, vbNullString, 1
    End With
    
End Sub

**analyst** · 02-05-2014, 02:31 PM

I replaced my code with your suggestion as under; but the outcome remains as it was earlier.

I mean, blank rows comes at the top of the range.

Code:

Sub OSumRank()

Sheets("OSum").Select
Application.ScreenUpdating = False
Columns("R:AC").EntireColumn.AutoFit

Dim r As Range
Set r = Range("ae3:ap17")

Const fNum = -9999999.99
    
    With r
        On Error Resume Next
        .SpecialCells(4).Value = fNum
        
        Range("R3:AC17").Copy
        Range("AE3").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Range("AE3:Ap17").Select
    
        Selection.Sort Key1:=Range("AE3"), Order1:=xlAscending, Key2:=Range( _
        "AL3"), Order2:=xlDescending, Key3:=Range("AM3"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
        
        .Replace fNum, vbNullString, 1
    End With

End Sub

**analyst** · 02-05-2014, 02:34 PM

Alternatively, is it possible that, after copy, pastevalue, macro can check the range, and if cell found to be empty, clear content, and then sort.

This I'm saying so because, if i manually press 'DEL' button over blank rows at the top, and re-run the macro, result comes at the top, and blank rows remains at the bottom, that is how i want.

**Admin** · 02-05-2014, 03:41 PM

Hi

try this one and let me know if you get any message box pop ups ?

Code:

Option Explicit

Sub OSumRank()

    Sheets("OSum").Select
    Application.ScreenUpdating = False
    Columns("R:AC").EntireColumn.AutoFit
    
    Dim r       As Range
    Dim bCells  As Range
    
    Range("R3:AC17").Copy
    Range("AE3").Select
    Selection.PasteSpecial Paste:=xlPasteValues
            
    Set r = Range("ae3:ap17")
    
    Const fNum = -9999999.99
    
    With r
        On Error Resume Next
        Set bCells = .SpecialCells(4)
        If Not bCells Is Nothing Then
            MsgBox dcell.Address
        Else
            .Sort Key1:=Range("AE3"), Order1:=xlAscending, Key2:=Range( _
            "AL3"), Order2:=xlDescending, Key3:=Range("AM3"), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
        End If
        .Replace fNum, vbNullString, 1
    End With

End Sub

**analyst** · 02-05-2014, 03:51 PM

No message box, macro gets executed, but result as it was earlier. Blank rows are at top.

**Admin** · 02-05-2014, 04:49 PM

Hi

Could you attach your workbook ?

**analyst** · 02-05-2014, 05:34 PM

Thanks for looking at here. The File with original simple vanila macro is attached herewith.

Just run the macro named 'sort', and see the result in range AE3:AP17

Original data, which are derived through Formulas are in range R3:AC17