-
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
-
Hi
Put some fictitious numbers in the blank cells before sorting and replace it after sorting.
-
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
-
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
-
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
-
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.
-
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
-
No message box, macro gets executed, but result as it was earlier. Blank rows are at top. :(
-
Hi
Could you attach your workbook ?
-
1 Attachment(s)
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
