Using R1C1 notation is simpler to use in a loop, don't have to worry about
column letters, it's all numbers.

..value = .Value

is a quick way of doing a Pastespecial Values, i.e change the formula to the
formula value.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Roger Govier" wrote in message
...
Hi Norman

I am still trying to improve my limited (but hopefully growing) VBA
skills.
Could you explain why you use the format

rng.FormulaR1C1 = "=R[-1]C"
and then use the For loop to set .value to .value

as opposed to
rng.value = "=R[-1]C"


--
Regards

Roger Govier



Norman Jones wrote:
Hi Chris,

Try:
'=============
Public Sub Tester001()
Dim rng As Range
Dim ar As Range

On Error Resume Next
Set rng = Range("A:C").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"

For Each ar In rng.Areas
With ar
.Value = .Value
End With
Next ar

End If

End Sub
'<<=============


---
Regards,
Norman


"Chris_t_2k5" wrote in message
...
Hi, I need a macro that will copy rows as follows.


I have values in Cells A1:C1, A101:C101, A201:C201 etc.....

All of the cells between 2-100, 102-200 ... are blank. I need a
macro that will copy cell A1:C1 and paste special as values below in
the range A2:C100
and the same for all subsequent rows below so in the end all the
blank spaces
will be full.

Thanks