Macro in Excel to autofill to a certain row, but from variable row?

Question

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("D3").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))

Answer 1

Try the following. No need to select the cells; just fully reference the cells/ranges.

Note that the space and underscore at the end of a line is a line break in an otherwise single line of code. (After AutoFill)

Dim lastrow As Long

    lastrow = Worksheets("Sheet1").Range("D3").End(xlDown).Row

    With Worksheets("Sheet1")

        .Cells(.Rows.Count, "C").End(xlUp).AutoFill _

           Destination:=Range(.Cells(.Rows.Count, "C").End(xlUp), .Cells(lastrow, "C"))

    End With

End Sub

Answer 2

Will this work though if I need them reference different values?

For instance:

A	1-
B	2-
A	3-
B	4-
B	5-
	6-

I would need this to autofill the 6th cell so that it says "B". 

But for the next 6, I could need it to say "C" instead.

Would the CBA work for this?

Thanks for the help

Answer 3

How you determine what letter you want to use in the AutoFill? You have given a couple of examples but I can't see the correlation so that the next letter can be determined by code so I need the rules for determining the next letter.

The answer that I provided should use the letter that is in the last used cell in column "C".

Answer 4

This is great! 

Thanks for that. 

Side note if possible, is there a way to have this formula select the Range "A:C" instead of just C? Or would the only way to do that be replicating the formula two more times, once for each Column?

Answer 5

No problem. Code below.

Some notes of explanation:

• I have re-dimensioned your variable to lastRowD  because it makes the code more self explanatory.
• Placed both lines of code to find last rows inside the "With/End With" statements.
• Provided an alternative line of code for last line in column C. Doesn't really matter which option you use but you should be aware that with xlDown then if there are any blank cells in the range then it stops at the blank cell. The Rows.Count/xlUp method always finds the last used cell in the column irrespective on any blanks in the range above it. You could also use Rows.Count/xlUp method to find the last used cell in column D.

    Dim lastRowD As Long    'Last used cell in column D

    Dim lastRowC As Long    'Last used cell in column C

     With Worksheets("Sheet1")

        lastRowD = .Cells(3, "D").End(xlDown).Row

        lastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row

        'lastRowC = .Cells(3, "C").End(xlDown).Row   'Alternative to previous line

        .Range(.Cells(lastRowC, "A"), .Cells(lastRowC, "C")).AutoFill _

            Destination:=.Range(.Cells(lastRowC, "A"), .Cells(lastRowD, "C"))

     End With