Copy rows macro

[Guest]

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

 

[Norman Jones]

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
'<<=============

 

[Guest]

Thanks, that worked perfectly

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

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 Govier]

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

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

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

 

[Bob Phillips]

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)

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

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

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 Govier]

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

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.

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

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


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

 

[Norman Jones]

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

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

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.

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


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

 

[Bob Phillips]

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)

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

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.

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


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 Govier]

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

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

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

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.


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


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