MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Why can't I sort these rows of data ascending???


Posted by Brian A. on April 11, 2001 5:09 PM

I'm trying to sort ascending some data from a macro.
I am selecting the entire rows based on two variables: ABCBegin and ABCEnd.

Here's the Code I am trying to use:

.Rows(ABCBegin & ":" & ABCEnd).Select
Selection.Sort Key1 = Range("A & ABCBegin"), Order1 = xlAscending

I'm trying on concatenate "A" for Column A then ABCBegin for the row number
so the macro knows what to sort by. This isn't working.

Please help if you know this!

thank You.

Brian


Posted by anon on April 11, 2001 5:14 PM


Rows(ABCBegin & ":" & ABCEnd).Sort Key1 = Range("A" & ABCBegin), Order1 = xlAscending

Posted by Brian A on April 11, 2001 5:20 PM

Just tried that, still errored out. :-(


I placed the code in my macro but it still does not sort. It errors out. Any more ideas? Maybe another way of sorting a range of rows?

Thanks in advance

Brian

Posted by anon on April 11, 2001 5:44 PM

Re: Just tried that, still errored out. :-(


I've just tested it and it worked. Try pasting into your macro again.

Rows(ABCBegin & ":" & ABCEnd).Sort Key1:=Range("A" & ABCBegin), Order1:=xlAscending


Posted by Dave Hawley on April 11, 2001 5:45 PM

Re: Just tried that, still errored out. :-(

Hi Brian

Your code is missing the Colons before the = sign:

Rows(ABCBegin & ":" & ABCEnd).Sort Key1:=Range("A" & ABCBegin), Order1:=xlAscending,Orientation:=xlTopToBottom


Also include the Orientation as well, because if the last setting used was LeftToRight it will default to this.


Dave


OzGrid Business Applications

Posted by anon on April 11, 2001 5:46 PM

Re: Just tried that, still errored out. :-(


I've just tested it and it worked. Try pasting into your macro again.

Rows(ABCBegin & ":" & ABCEnd).Sort Key1:=Range("A" & ABCBegin), Order1:=xlAscending


Just noticed that the original code I posted had "=" unstead of ":="


Posted by Brian A. on April 12, 2001 8:19 AM

Re: Just tried that, still errored out. :-(

Still having problems. The goo dnews is the code does not error out anymore, but the rows are still not sorted.
Almost like the rows never get selected so they never get sorted. Here's the line of code I'm using:

Rows(SMCEndSort & ":" & CNCEnd).Sort Key1:=Range("A" & SMCEndSort), Order1:=xlAscending, Orientation:=xlTopToBottom

Is there a way to select the rows first, then try to sort them?
I tried

Rows(SMCEndSort & ":" & CNCEnd).Select
Selection.Sort Key1:=Range("A" & SMCEndSort), Order1:=xlAscending, Orientation:=xlTopToBottom

This bombed on the sort.

Thanks for all your help.

Posted by Brian A on April 12, 2001 8:20 AM

Still having problems. The goo dnews is the code does not error out anymore, but the rows are still not sorted.
Almost like the rows never get selected so they never get sorted. Here's the line of code I'm using:

Rows(SMCEndSort & ":" & CNCEnd).Sort Key1:=Range("A" & SMCEndSort), Order1:=xlAscending, Orientation:=xlTopToBottom

Is there a way to select the rows first, then try to sort them?
I tried

Rows(SMCEndSort & ":" & CNCEnd).Select
Selection.Sort Key1:=Range("A" & SMCEndSort), Order1:=xlAscending, Orientation:=xlTopToBottom

This bombed on the sort.

Thanks for all your help.

Posted by H. Gilder on April 12, 2001 8:33 AM

Re: Just tried that, still errored out. :-(


Brian A.
Whether or not you select the rows first makes no difference. It is better not to select (it increases the run time).
You are doing something wrong - there's nothing wrong with the code.
Check your range variables.
Herbert

Posted by brian a on April 12, 2001 8:41 AM

Re: Just tried that, still errored out. :-(

Herbert -

SMCEndSort has a runtime value of 734 at runtime, and CNCEnd has a value of 1350 at runtime. They are both defined as integer. Should they be defined as range?

Any other ideas?

Posted by H. Gilder on April 12, 2001 9:28 AM

Don't know !

Brian
No, they should not be defined as range.
I've tested your code and it works. Don't know what else to say.
Herbert

Posted by brian a on April 12, 2001 10:27 AM

gOT IT!!!

ActiveSheet.Rows(SMCEndSort & ":" & CNCEnd).Select
Selection.Sort Key1:=Selection.Range("A" & SMCEndSort), Header:=xlNo

This is the code I inserted to make it work. It seems I have some screwy uses of selection in my code.

In any case, thanks to all of you who helped me. I appreciate it!