Writing Macro using R1C1 reference... 1

[Eurobum]

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&quot.Select
Selection.End(xlDown).Select ---> Start Cell
Range("AG458&quot.Select
Selection.End(xlDown).Select ---> End Cell


Range("C459:AG459&quot.Select ---> to be replace with start cell address and end cell address

Selection.AutoFill Destination:=Range("C459:AG460&quot, Type:=xlFillDefault

I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address&quot 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.

 

[cactus13]

I don't think the working of the range object is all clear to you. The ay you use it works for absolute cell reference. For relative cell reference it works a little different.

First of all:

[tt]Range("C458&quot.Select
Selection.End(xlDown).Select
Range("AG458&quot.Select
Selection.End(xlDown).Select[/tt]

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:

[tt]I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address&quot in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.[/tt]

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

 

[Mala]

Tho' it's not very clear from your query, I take it that:
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&quot.End(xlUp).Select
ActiveCell.EntireRow.Range("C1:AG1&quot.Select
Selection.AutoFill Destination:=Selection.EntireRow.Range("C1:AG2&quot, Type:=xlFillDefault
End Sub

It should work...

 

[Eurobum]

Thanks a lot...Cactus & Mala...

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.

 

[Eurobum]

Mala...Thank you very much...

It works like a charm. I will try to understand those lines at later time.