macro copying formula in active rows
Andy
Are there any blank rows in your data? If so, this will not work, otherwise
try this:
Range("X2",Range("Y2").End(xlDown).Offset(0,1)).FormulaR1C1 = "=SUM etc...
This assumes that column Y will contain information for each row that you
want to put this formula in. If another column would be better, change the
"Y2" to whatever - then be sure to change the Offset to get back over to
column X. It's generally not necessary to select a cell before you perform
some operation on it, so I combined your two statements in to one.
HTH
{*filter*} K.
Quote:
> I have a macro that adds up five columns of numbers using the following:
> ' Calculate Total Hours
> Range("X2").Select
> ActiveCell.FormulaR1C1 = "=SUM(RC[-21]:RC[-17])"
> Which works fine for the first row. I then need to copy this down the
> sheet for all of the rows that contain data, which can be anything from
> 1 to thousands. I recall a thread about "Active Ranges" but I am not
> sure how to apply this in this instance.
> I have the following work around which works by copying the formular
> down the entire column, then deleteing rows, based on whether a coulumn
> is blank, but this is quite slow.
> Range("X2").Select
> Selection.Copy
> Columns("X:X").Select
> ActiveSheet.Paste
> Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete (xlUp)
> Any help and suggestions to speed this up would be greatly recieved.
> Andy
