sort in vb

[JIM.H.]

Hello,
Here is my sorting code produced by macro:

Range("A1:H6895").Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:= _
Range("G2"), Order2:=xlAscending, Key3:=Range("H2"),
Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

How can I make the Range("A1:H6895").Sort dynamic, based on the number of
rows in excel sheet?
Thanks,

[Bernie Deitrick]

Jim,

A few ways:

Change Range("A1:H6895"). to Range("A1"). If you have no blank rows or columns, Excel will pick up
the current area. Which leads us to:

Change Range("A1:H6895"). to Range("A1:H6895").CurrentRegion

Or pick up the last row through code:
Change Range("A1:H6895"). to Range("A1:H" & Range("H65536").End(xlUp).Row)

Or find the bottom-most cell of column H:
Change Range("A1:H6895"). to Range("A1", Range("H65536").End(xlUp))

HTH,
Bernie
MS Excel MVP


"JIM.H." <[email protected]> wrote in message
news:[email protected]...
> Hello,
> Here is my sorting code produced by macro:
>
> Range("A1:H6895").Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:= _
> Range("G2"), Order2:=xlAscending, Key3:=Range("H2"),
> Order3:=xlAscending _
> , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
> xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
> DataOption3:=xlSortNormal
>
> How can I make the Range("A1:H6895").Sort dynamic, based on the number of
> rows in excel sheet?
> Thanks,
>

[David McRitchie]

One more way and a lot safer because a blank row would
otherwise stop your data and worse a blank column would
exclude the cells to the right of the blank column and destroy
the integrity of your data.

change Range("A1:H6895"). to cells.

and don't use xlguess for headers, either you have them
or you don't.
http://www.mvps.org/dmcritchie/excel/sorting.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm