Varying Data Sort Macro.

Hi,
I am trying to create a macro that does a data sort for me.
The problem I am having is that the data starts from say cell A6 and the end
varies depending on how many new records have been added... new records are
always added at the end.
I was thinking is it possible to put a value say "valuehere" in the cell
below the last field/record so that even when inserting a row that value will
always be there at the end of the data as the row will be inserted above
that... Sorry im confussing myself here !! reson being is there then a way to
say in the macro find valuehere and return the "Cell value" then select
range A6:"Cell Value" then data sort say by col C.... ie.

1 A 10
2 B 20
Valuehere

or if a row is inserted then

1 A 10
2 B 20
3 C 30
Valuehere

I hope this makes sence and someone can help, there is probably a big time
easier answer please help...

Regards

Mike

How about

set rng = Range(range("A6"),range("A6").End(xldown)).resize(,2)
rng.Sort Key1:=Range("A6")
this would give you what you describe without having the valuehere entry.

change the 2 to reflect the number of columns you have.

if you want the valuehere entry

set rng1 = Cells.Find("valuehere")
if not rng1 is nothing then
set rng = Range(ange("A6"), rng1)
rng.Sort Key1:=Range("A6")

--
Regards,
Tom Ogilvy

"MyKool" <MyKool@discussions.microsoft.com> wrote in message
news:6140DC72-AB4B-47D5-B80B-2BB264A05AB5@microsoft.com...
> Hi,
> I am trying to create a macro that does a data sort for me.
> The problem I am having is that the data starts from say cell A6 and the
end
> varies depending on how many new records have been added... new records
are
> always added at the end.
> I was thinking is it possible to put a value say "valuehere" in the cell
> below the last field/record so that even when inserting a row that value
will
> always be there at the end of the data as the row will be inserted above
> that... Sorry im confussing myself here !! reson being is there then a way
to
> say in the macro find valuehere and return the "Cell value" then select
> range A6:"Cell Value" then data sort say by col C.... ie.
>
> 1 A 10
> 2 B 20
> Valuehere
>
> or if a row is inserted then
>
> 1 A 10
> 2 B 20
> 3 C 30
> Valuehere
>
> I hope this makes sence and someone can help, there is probably a big time
> easier answer please help...
>
> Regards
>
> Mike
>

Hi,
Try:

Lastrow=Cells(rows.count,"A").end(xlup).row <==== change column if required

and set your sort range to

Range("A6:A" & lastrow)

HTH

"MyKool" wrote:

> Hi,
> I am trying to create a macro that does a data sort for me.
> The problem I am having is that the data starts from say cell A6 and the end
> varies depending on how many new records have been added... new records are
> always added at the end.
> I was thinking is it possible to put a value say "valuehere" in the cell
> below the last field/record so that even when inserting a row that value will
> always be there at the end of the data as the row will be inserted above
> that... Sorry im confussing myself here !! reson being is there then a way to
> say in the macro find valuehere and return the "Cell value" then select
> range A6:"Cell Value" then data sort say by col C.... ie.
>
> 1 A 10
> 2 B 20
> Valuehere
>
> or if a row is inserted then
>
> 1 A 10
> 2 B 20
> 3 C 30
> Valuehere
>
> I hope this makes sence and someone can help, there is probably a big time
> easier answer please help...
>
> Regards
>
> Mike
>