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