Hi Norman

If you were to look up in the sky, there would be an enormous red glow,
emanating from my crimson face<vbg.
I must have been having a very senior moment this morning (correct that,
this day).
Of course, I didn't look at the cells on the sheet, merely the view that
was visible of filled cells as per the entries on rows 1, 101, 201.
I can see that changing the formulae to values is necessary.

Adding the line
Rng.value = Rng.value
(to my erroneous modficiation of your sub) produced very strange
results!!!
Rows 2:100 were turned to copies of Row 1, Rows 102:104 were copies of
Row1, not 101.
Rows 105:200 were #N/A, and rows 202:300 were copies of Row1

Thank you (and Bob) for the enlightenment.

--
Regards

Roger Govier



Norman Jones wrote:
Hi Roger,

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.

Does this not fill the blank cells with a formula rather than the
corresponding value?

I was really asking, why it is necessary to do it in 2 parts?

Try, with your code, adding the line:

Rng.Value = Rng.value

and examine the results.


---
Regards,
Norman



"Roger Govier" wrote in message
...
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