Varying Data Sort Macro.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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")
 
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
 
Back
Top