R1C1 referencing

[Sandy]

Hi

The following in A1 notation needs to be converted to R1C1 notation, such
that I can add a 1 to the row part of A29. In other words the formula in A2
needs to be copied down 1 extra cell each time a macro runs.

Range("A2").Select
Selection.Copy
Range("A3:A29").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Select
Selection.Copy
Range("C3:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Would this be the way to do it (x is arrived at from counting the non-blank
cells in a column)

Range(Cells(2, 1)).Select
Selection.Copy
Range(Cells(3, 1), Cells(x + 1, 1)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(2, 3)).Select
Selection.Copy
Range(Cells(3, 2), Cells(x + 1, 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sandy

 

[Guest]

I did a bit of quick cleanup. I'm sure there's more

Range("A2").Copy
Range("A3:A29").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Copy
Range("C3:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

This determines the last row in column C (for example)
lrow= cells(rows.count,3).end(xlup).row

You could change your range to

Range("C3:C" & lrow + 1).Select

 

[Sandy]

Excellent thank you
Sandy

I did a bit of quick cleanup. I'm sure there's more

Range("A2").Copy
Range("A3:A29").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Copy
Range("C3:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

This determines the last row in column C (for example)
lrow= cells(rows.count,3).end(xlup).row

You could change your range to

Range("C3:C" & lrow + 1).Select

 

[Gord Dibben]

Without all the selects and pastes.

lrow = Cells(Rows.Count, 3).End(xlUp).Row
ActiveSheet.Range("A2").Copy _
Destination:=ActiveSheet.Range("C3:C" & lrow + 1)

But I don't understand why A3:A29 was selected for first paste then C3:C29 for
second paste.


Gord Dibben MS Excel MVP

 

[Sandy]

Works great thank you

I did a bit of quick cleanup. I'm sure there's more

Range("A2").Copy
Range("A3:A29").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Copy
Range("C3:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

This determines the last row in column C (for example)
lrow= cells(rows.count,3).end(xlup).row

You could change your range to

Range("C3:C" & lrow + 1).Select