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

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

Thanks, that worked perfectly

"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

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

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

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

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

Roger,

The problem with what you are suggesting is that the range that gets
populated by formulae maybe (will be according to the OP's scenario)
non-contiguous. Because of this, the value of the rng will only be the first
area in the collection of areas, and so the second area (second block of the
non-contiguous blocks) will get overwritten with the first area's values,
not their own. That is why Norman loops through the areas, not just relying
on the range.

If there were just one area in the resultant range, your suggestion works
fine, but you cannot rely on it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"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