I am designing a macro for a user that takes a range of data and moves it
from one column to another and then clears the data from the original column
at the beginning of the day. The problem is that new rows are added every few
days and the range in the macro does not update. This causes data not to be
moved at start of day. I have formulas below the range I want to move so I
can't set the range to large as to clear out the formulas. Does anyone know
how to have the range in the macro change as the range in the worksheet
changes?

Create a dynamic named range and then reference that named range in your macro.

Info on dynamic named ranges he
http://www.ozgrid.com/Excel/DynamicRanges.htm

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Mark2122" wrote:

I am designing a macro for a user that takes a range of data and moves it
from one column to another and then clears the data from the original column
at the beginning of the day. The problem is that new rows are added every few
days and the range in the macro does not update. This causes data not to be
moved at start of day. I have formulas below the range I want to move so I
can't set the range to large as to clear out the formulas. Does anyone know
how to have the range in the macro change as the range in the worksheet
changes?

If the rows all contain data - i.e., no empty cells - you can use something
along the lines of

dim rngBottom as Range
set rngBottom = range("a1").End(xlDown)

to find the bottom cell in column A. If that row contains your formulas,
and you need to exclude two rows, modify it like so

set rngBottom = range("a1").End(xlDown).offset(-2,0)


Then, to move that data somewhere else

Range("A1", rngBottom).Cut

Range("C1").Select
ActiveSheet.Paste

"Mark2122" wrote:

I am designing a macro for a user that takes a range of data and moves it
from one column to another and then clears the data from the original column
at the beginning of the day. The problem is that new rows are added every few
days and the range in the macro does not update. This causes data not to be
moved at start of day. I have formulas below the range I want to move so I
can't set the range to large as to clear out the formulas. Does anyone know
how to have the range in the macro change as the range in the worksheet
changes?