Thanks Bob

I realise that the .value -.value is changing the formula to a value.
I can understand, but hadn't realised, that the R1C1 notation was
faster.
I tried Norman's code and it worked fine.
I then tried changing the rng.FormulaR1C1 = "=R[-1]C"
to rng.value = "=R[-1]C"

and skipped the For loop doing the .value =.value part, and it seemed to
work as well.
I was really asking, why it is necessary to do it in 2 parts?

--
Regards

Roger Govier



Bob Phillips wrote:
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.


"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