I want to copy a formuala down a column. There maybe blank spaces in
my column. My last row is 5000 but may grow maybe to 10000. I have not
seen it coded like this (using Offset and End xlUp) before but it
appears to work. Is this OK to code like this? Other people seem to
code using something like this -
Range(Range("A2"),Cells(rows.count,"A").End(xlup)) .

Thanks


For Each cll In Range("q6:da6")
If cll.HasFormula = True Then
cll.Copy
Range(cll, cll.Offset(30000, 0).End(xlUp)).PasteSpecial

End If
Next

Snax,

The answer is, of course, "It depends."

The code that you posted would work to copy over any cells that are already
filled. If the formulas need to be copied down further, then it won't do
that.

Here's code that should work: you don't need the conditional:

Dim myRow As Long
Dim myCell As Range

'Change the A to a column that sets your table size
myRow = Range("A65536").End(xlUp).Row
For Each myCell In Range("q6:da6").SpecialCells(xlCellTypeFormulas)
myCell.Copy Range(myCell, Cells(myRow, myCell.Column))
Next myCell





--
HTH,
Bernie
MS Excel MVP
"snax500" wrote in message
om...
I want to copy a formuala down a column. There maybe blank spaces in
my column. My last row is 5000 but may grow maybe to 10000. I have not
seen it coded like this (using Offset and End xlUp) before but it
appears to work. Is this OK to code like this? Other people seem to
code using something like this -
Range(Range("A2"),Cells(rows.count,"A").End(xlup)) .

Thanks


For Each cll In Range("q6:da6")
If cll.HasFormula = True Then
cll.Copy
Range(cll, cll.Offset(30000, 0).End(xlUp)).PasteSpecial

End If
Next