Flexible autofill in macro

[Pluggie]

Hi.

I have a macro, that adds in two columns a value "Bestellijst" and "CTR".

After that I want the macro to autofill these values for every row in the
spreadsheet.

The number of rows vary. It could be 10, 346, 4659 or any other number.
When first recording the macro it stored the exact range at the time...
somthing like "C23476" for this autofill.

I changed it already into the code below.
The problem now however... is that it autofills until the last row excell
can handle.
How can I get it to autofill only until the row with the last record each
time I run the macro, independant of the number of records?

---code---
Range("C2").Select
ActiveCell.FormulaR1C1 = "Bestellijst"
Range("D2").Select
ActiveCell.FormulaR1C1 = "CTR"
Range("C22").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
---/code---

Thanks for your help.

 

[Per Jessen]

Hi

I suppose you have data in column A, so we can use this column to determine
LastRow.

Also I removed the "Select" statements as they are not needed, and will slow
down your macro:

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C2").FormulaR1C1 = "Bestellijst"
Range("D2").FormulaR1C1 = "CTR"
Range("C22").AutoFill Destination:=Range("C2" & LastRow)

Hopes this helps.
....
Per

 

[Pluggie]

Yes it did... Thanks a lot.

Hi

I suppose you have data in column A, so we can use this column to determine
LastRow.

Also I removed the "Select" statements as they are not needed, and will slow
down your macro:

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C2").FormulaR1C1 = "Bestellijst"
Range("D2").FormulaR1C1 = "CTR"
Range("C22").AutoFill Destination:=Range("C2" & LastRow)

Hopes this helps.
....
Per

 

[drmrheintz]

I have the same problem. I tried Pluggie's new code but its not working for me. Can you help? I have data in column C (just various values) and I want column D to be the column C x 0.064516 to the end of the column where Column C has data. Both Columns A& B also have same # rows. When I record the macro, it comes out to be:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*0.064516"
Range("D2").Select
Range
Selection.AutoFill Destination:=Range(Selection, Selection.End(x1down)).Select

I'm new to this so please excuse me if this is a simple question.