Sort rows within a range

NewToVba

Hi

My problem is the following.

I have a (100rows x 10columns) range to which some numerical output is written. I need to access each row in this range and sort it descending. Any suggestions Any help is much appreciated. Thanks.

Thomas

Joe Dawson

Do you mean you want to work through each row in the range, sort it left to right, then move down to the next row and sort it left to right, and so on

NewToVba

Hi Joe

Yes, you are right, that is precisely what I need to do.

NewToVba

Hi again

I have written a solution that works. If any of you have comments, they are also much appreciated - I have just started with VBA.

In a "Do While Loop" statement, this is the code:

***

Range(Cells(Counter + 1, 6), Cells(Counter + 1, 6).End(xlToRight)).Sort Key1:=Range(Cells(Counter + 1, 6), Cells(Counter + 1, 6)), Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

***

The "Counter" variable keeps track of the rows.

References are to cells and not a named range, which would have been kind of cool, I think.

Andy Pope

Hi,

Pretty good code if you have only just started ;)

One thing to be aware of, and this depends totally on your data, is the use of .End(xRight) will cause problems if there are empty cells in the range of cells being sorted. If the end of each row varys it may be better to find the end cell by coming at it from the far right using xlToLeft.
Also you are adding +1 to the counter when specifying the range and sortkey. It would be better to increment the counter once before using it and then remove all the +1's. If this impacts on code lines further on in your routine you can always create another variable to hold the value.

Counter = Counter + 1
Range(Cells(Counter, 6), Cells(Counter, Columns.Count).End(xlToLeft))

If you have a named range for you data then you could use this.

Dim rngRow as Range

For Each rngRow In Range("NamedRange").Rows
rngRow.Sort Key1:=rngRow.Cells(1), Order1:=xlDescending, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
Next

NewToVba

Hi Andy

Thanks. Very good comments - just what I was thinking about. Right now I am relying on the Help Function and the Internet to learn about VBA coding. Any ideas for literature references I am a student of finance, so my needs are mostly related to management of data files and calculations.

Thanks! Best, Thomas

Andy Pope

For pure vba learning material your in safe hands with John Walkenbach's books.

Excel VBA Programming For Dummies
http://j-walk.com/ss/books/bookxl27.htm

Excel 2003 Power Programming With VBA
http://j-walk.com/ss/books/xlbook25.htm

Search terms "excel financial" at amazon returns a few choices.

Sort rows within a range

Re: Sort rows within a range

Re: Sort rows within a range

Re: Sort rows within a range

Re: Sort rows within a range

Re: Sort rows within a range

Re: Sort rows within a range