Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sort is placing blanks at top
I have a list of formulas in cells A4:A53. The result of each formula will
be either a text description, or "blank" depending on the result of the IF statement contained within. The problem is, I have a macro that copies the 'values' of range A2:A50 into B4:B53, then tries to sort ascendingly. When I do this, all of the "blanks" appear at the top while the remaining text descriptions fall ascendingly below. I need the "blanks" to be at the bottom, but cannot figure out how to do this. FYI, when you look in the cells of the "blanks", there is an apostrophe to indicate left-alignment. I think this is causing the issue, but can't figure out what to do---I have tried formatting B2:B50 both as "general" and "text"---both end up with the same result. Here is the code I am using: Range("A4:A53").Select Selection.Copy Range("B4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveWorkbook.Worksheets("Custom").Sort .SetRange Range("B4:B53") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("C1").Select Application.CutCopyMode = False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sort is placing blanks at top
Have the macro do exactly what you would do manually. In B1 enter:
=IF(A1="",2,1) and copy down. The sort both columns first by column B and then by column A. If we start with: joe 1 2 max 1 2 fred 1 2 claence 1 2 albert 1 2 zoe 1 we will end up with: albert 1 claence 1 fred 1 joe 1 max 1 zoe 1 2 2 2 2 2 -- Gary''s Student - gsnu200855 "jday" wrote: I have a list of formulas in cells A4:A53. The result of each formula will be either a text description, or "blank" depending on the result of the IF statement contained within. The problem is, I have a macro that copies the 'values' of range A2:A50 into B4:B53, then tries to sort ascendingly. When I do this, all of the "blanks" appear at the top while the remaining text descriptions fall ascendingly below. I need the "blanks" to be at the bottom, but cannot figure out how to do this. FYI, when you look in the cells of the "blanks", there is an apostrophe to indicate left-alignment. I think this is causing the issue, but can't figure out what to do---I have tried formatting B2:B50 both as "general" and "text"---both end up with the same result. Here is the code I am using: Range("A4:A53").Select Selection.Copy Range("B4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveWorkbook.Worksheets("Custom").Sort .SetRange Range("B4:B53") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("C1").Select Application.CutCopyMode = False |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sort is placing blanks at top
Sub test()
With Range("A4:A53") .Copy .PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False .Sort _ Order1:=Range("B4"), _ Header:=xlNo, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ SortMethod:=xlPinYin End With FirstRow = Range("B4").End(xlDown).Row If FirstRow < 4 Then Rows("4:" & (FirstRow - 1)).Delete End If End Sub "jday" wrote: I have a list of formulas in cells A4:A53. The result of each formula will be either a text description, or "blank" depending on the result of the IF statement contained within. The problem is, I have a macro that copies the 'values' of range A2:A50 into B4:B53, then tries to sort ascendingly. When I do this, all of the "blanks" appear at the top while the remaining text descriptions fall ascendingly below. I need the "blanks" to be at the bottom, but cannot figure out how to do this. FYI, when you look in the cells of the "blanks", there is an apostrophe to indicate left-alignment. I think this is causing the issue, but can't figure out what to do---I have tried formatting B2:B50 both as "general" and "text"---both end up with the same result. Here is the code I am using: Range("A4:A53").Select Selection.Copy Range("B4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveWorkbook.Worksheets("Custom").Sort .SetRange Range("B4:B53") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("C1").Select Application.CutCopyMode = False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO YOU SORT EXCEL FILE WITH BLANKS | Excel Worksheet Functions | |||
comparing two cols and placing blanks. | Excel Discussion (Misc queries) | |||
How do I sort a list that contains blanks that I want to keep? | Excel Discussion (Misc queries) | |||
Sort a row of strings, some containing blanks | Excel Programming | |||
Can I remove blanks from a range without using sort? | Excel Discussion (Misc queries) |