Data Clean-up

[garret]

I am doing a spredsheet with several different sets of data. These
sets of data are of varying lengths so I recorded a macro that imported
the data and formated it. Because the data is of varying lengths the
macro drags some formulas down to a certain row that will cover all of
the data. My problem is I would like to record a macro that will
delete all of the draged formulas that go past the end of the data set.
Just clean up the rows that don't have the data in them. Can any one
help me?

 

[Stephen Rasey]

Can you first delete the formulas from the 2nd row to the bottom?
Then copy the formulas down the current range.

Set rngDataTop = cell that is the top of the data range.
Set rngFormulaTop = cell that is the top of the formula range you want to
copy down
Set rngData = Range(rngDataTop,rngDataTop.end(xldown))
Set rngFormulas = range(rngFormlaTop,rngFormulaTop.end(xldown))
nrows = rngData.rows.count

'clear the formulas
rngFormulas.offset(1,0).clear

'Copy the formulas to the length of data.
rngFormulaTop.copy rngFormulaTop.offset(1,0).resize(nrows-1)

-Stephen Rasey
Houston