Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Varying Data Sort Macro.
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Varying Data Sort Macro.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
varying table length in a MACRO | Excel Discussion (Misc queries) | |||
Formatting/Totalling Macro with varying rows of data | Excel Discussion (Misc queries) | |||
Macro: how to set a print area of varying size? | Excel Discussion (Misc queries) | |||
Using Varying Range Size in Macro | Excel Programming | |||
Varying a macro based on a cell's value | Excel Programming |