Autofill down from a variable cell

[BillyH]

I'd like to create a template that can copy and paste data, while autofilling data next to it.



The data I have would be pasted in random amounts, so I have no set Range to reference. I have part of some code that lets me paste down to the last row of another column, but the code I have won't let me also select a variable Range to autofill down to the other column.

I'd like to select the last cell in C, then have it autofill to the lastrow in D (Which is already set up in the first part of the Macro.



Here's the current code for one part of my macro:




Dim lastrow As Long

lastrow = Worksheets("Sheet1").Range("D4").End(xlDown).Row
With Worksheets("Sheet1").Range("C4").Select
Selection.End (xlDown)
.AutoFill Destination:=Range("C4:C" & lastrow&)
End With





But instead of Range(C4) I'd like it to be a variable Range - sometimes it may be at C56, then the next it could be at C20.

Something such as - Range ("C" & Cells(Rows.Count, "C").End(xlUp))

I've read through a few posts on autofilling, but none have provided a formula that changes where the autofill is last selected from

 

[Michael M]

So you want to copy from the last used row in "C" down to the equivalent last used row in "D" ???

 

[BillyH]

So you want to copy from the last used row in "C" down to the equivalent last used row in "D" ???

Yes. That about sums it up.

To give more background, I have another part of the code that is copying and pasting some values down in "D", then I would need "C" to be autofilled only as far as D was copied. This happens multiples times. Copy a bunch of values in "D" drop down formula in "C" -repeat.

 

[Michael M]

maybe this

Sub MM1()
Dim lr As Long, lr2 As Long
lr = Range("D4").End(xlDown).Row
lr2 = Range("c4").End(xlDown).Row
Range("C" & lr2).AutoFill Destination:=Range("C" & lr2 & ":C" & lr)
End Sub