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

Sort by Alpha, ignore blanks
 

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

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

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.

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}