Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by Alpha, ignore blanks
I have this code, but realize that the blanks are sorted to the top
How can I prevent this. They need to stay at the bottom I need the blanks, to add rows of names in the future. After each new name is entered, it should rearrange the data in the cells specified in alphabetical order of the names. Sub Alpha() ActiveWindow.LargeScroll ToRight:=3 Range("AS2:DS100").Select ActiveWindow.ScrollRow = 1 Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.SmallScroll ToRight:=-3 ActiveWindow.ScrollColumn = 1 Range("C8").Select End Sub Thank you for any suggestions |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by Alpha, ignore blanks
Thank you Tushar,
changed the range from AS2:DS2 to AS2:AS100 and its working like a charm. "Tushar Mehta" wrote: Depending on how new information is entered, one possible solution would be to sort only the range containing data. Sub Alpha() Range(Range("AS2:DS2"), Range("AS2:DS2").End(xlDown)).Sort _ Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have this code, but realize that the blanks are sorted to the top How can I prevent this. They need to stay at the bottom I need the blanks, to add rows of names in the future. After each new name is entered, it should rearrange the data in the cells specified in alphabetical order of the names. Sub Alpha() ActiveWindow.LargeScroll ToRight:=3 Range("AS2:DS100").Select ActiveWindow.ScrollRow = 1 Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.SmallScroll ToRight:=-3 ActiveWindow.ScrollColumn = 1 Range("C8").Select End Sub Thank you for any suggestions |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by Alpha, ignore blanks
"ufo_pilot" wrote in message
... I have this code, but realize that the blanks are sorted to the top How can I prevent this. They need to stay at the bottom I need the blanks, to add rows of names in the future. After each new name is entered, it should rearrange the data in the cells specified in alphabetical order of the names. Sub Alpha() ActiveWindow.LargeScroll ToRight:=3 Range("AS2:DS100").Select ActiveWindow.ScrollRow = 1 Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.SmallScroll ToRight:=-3 ActiveWindow.ScrollColumn = 1 Range("C8").Select End Sub Thank you for any suggestions Better would be to write your own sort code, the performance will be better. See http://www.standards.com/index.html?Sorting for a demo that shows how easy it is to improve on Excel's sort. Code is included for several sorting algorithms. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by Alpha, ignore blanks
You are welcome. Glad to be of help.
-- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Thank you Tushar, changed the range from AS2:DS2 to AS2:AS100 and its working like a charm. {snip} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median - Ignore blanks | Excel Discussion (Misc queries) | |||
Ignore Blanks | Excel Discussion (Misc queries) | |||
How do I sort alpha neumeric fields that have an alpha suffix? | Excel Worksheet Functions | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) | |||
Ignore Blanks | Excel Programming |