Writing Macro using R1C1 reference...
Writing Macro using R1C1 reference...
(OP)
I'm writing a small macro to copy and paste the partial row of data. The data will be updated everyday i.e. row 30 will row 31 tomorrow. I'm using a macro to indentify the poistion of the beginning cell and the end cell but I don't know how to put it in the Range() function.
Range("C458").Select
Selection.End(xlDown).Select ---> Start Cell
Range("AG458").Select
Selection.End(xlDown).Select ---> End Cell
Range("C459:AG459").Select ---> to be replace with start cell address and end cell address
Selection.AutoFill Destination:=Range("C459:AG460"), Type:=xlFillDefault
I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address") in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.
Thanks for your help and sorry for this long message.
Range("C458").Select
Selection.End(xlDown).Select ---> Start Cell
Range("AG458").Select
Selection.End(xlDown).Select ---> End Cell
Range("C459:AG459").Select ---> to be replace with start cell address and end cell address
Selection.AutoFill Destination:=Range("C459:AG460"), Type:=xlFillDefault
I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address") in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.
Thanks for your help and sorry for this long message.





RE: Writing Macro using R1C1 reference...
First of all:
Range("C458").Select
Selection.End(xlDown).Select
Range("AG458").Select
Selection.End(xlDown).Select
I don't understand why you should use the Selection.End(xlDown).Select method if you don't do anything with it.
If you want to store the adress of this cell as a variable for future use you should insert something like:
Address=ActiveCell.Address
Second:
I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address") in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.
You can not rename absolute adresses to R?C? addresses. The method useing the R?C? command defines relative movement based on the cell you selected before you executed this command.
To do relative movement you should use this command:
ActiveCell.Offset(RowOffset, ColOffset)
Good luck!
Jonathan
RE: Writing Macro using R1C1 reference...
1. Your database extends from col 'C' to col. 'AG'
2. Everyday u intend to fill the first non-empty row in this range with Autofill of type xlFillDefault
If my assumption is correct, try the following:
Sub MyAutoFill()
Range("C65535").End(xlUp).Select
ActiveCell.EntireRow.Range("C1:AG1").Select
Selection.AutoFill Destination:=Selection.EntireRow.Range("C1:AG2"), Type:=xlFillDefault
End Sub
It should work...
RE: Writing Macro using R1C1 reference...
I will try your ways. They sent me out to the field again.
I'll be back in my desk tonight and I will give it a shot.
I will let you guys know the result. Thanks again.
RE: Writing Macro using R1C1 reference...
It works like a charm. I will try to understand those lines at later time.