Sorting usedsheet with macro

Originally Posted by clearwaterdave

I am too new to excel vba to understand a lot of the code I see here which makes it very difficult to find what I need. I have a macro that when executed, sorts my sheet by cols a, b and c. I created it by using the recorder to sort those columns. The problem is that it comes bach with

Range("A1:H345").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

which works, but only until I add cols or lines. When I add cols, the range of the sort fails to include the new columns because of the hard coding of col/line numbers. I can't figure out the syntax of how to have it dynamically sort the sheet based on its current used area.

Any help would be appreciated.

Thanks.

PS Is there a way to have this forum notify me when this thread is updated? I haven't been able to figure that out either. Until I figure that out, if you would copy clearwater@pobox.com with your reply, it would be much appreciated.

Thank you for the courteous tip. That helped a lot. In the future I'll use the code wrap. Now all I need is a good tutorial on vba for Excel so I don't get drowned in the syntax.

I would recommend dynamically naming the range that is to be sorted, then use the range name in your code.

See links for dynamic ranges:

http://www.cpearson.com/excel/named.htm#Dynamic
http://www.contextures.com/xlNames01.html#Dynamic
http://www.ozgrid.com/Excel/DynamicRanges.htm

Also, for the code, instead of

Please Login or Register  to view this content.

you would use something like

Please Login or Register  to view this content.

Using a dynamic range will allow the range to constantly change without you having to change the range in your procedure.

HTH

Jason

Originally Posted by royUK

You can define the range within the code

Please Login or Register  to view this content.

Or you can use ActiveSheet.UsedRange if the sheet only contains the table.

I would prefer to use your last suggestion as it appears to be far simpler. How would I substitute that range with:?

Please Login or Register  to view this content.

Also, what is the significance of Key1:=Range("F2")? I haven't specified that anywhere, yet that's what the macro recorder gave me.

Originally Posted by VBA Noob

Now open awaiting clearwaterdave to compile with rules by wrapping text. Please do not reply as your post will be deleted

VBA Noob

Am I missing something? Am I talking to a stone wall? Just how many times do I have to shout YES, I COMPILE (?) WITH RULES BY WRAPPING TEXT!

Or are you just trying to intimidate me for some reason I have yet to understand?????