Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Sort Range Using VBA And Ignoring Or Discarding Blank Rows

  1. #1
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12

    Sort Range Using VBA And Ignoring Or Discarding Blank Rows

    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
    However, if there are any blank cells within the range, it comes at the top, after it gets sorted.

    How to avoid blank rows/cells at top?

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-25-2024 at 01:47 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

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

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    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
    Last edited by analyst; 02-05-2014 at 01:48 PM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    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

  6. #6
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    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.

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Last edited by Admin; 02-05-2014 at 03:43 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    No message box, macro gets executed, but result as it was earlier. Blank rows are at top.

  9. #9
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Could you attach your workbook ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  10. #10
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    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
    Attached Files Attached Files
    Last edited by analyst; 02-05-2014 at 05:37 PM.

Similar Threads

  1. Delete blank rows
    By dhivya.enjoy in forum Excel Help
    Replies: 5
    Last Post: 10-23-2013, 10:04 PM
  2. Insert blank rows based on cell value
    By muhammad susanto in forum Excel Help
    Replies: 13
    Last Post: 09-11-2013, 06:18 AM
  3. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  4. Replies: 2
    Last Post: 05-06-2011, 02:59 AM
  5. Deleting blank rows
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-14-2011, 03:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •