Find end of variable range

Question

I am new to VBA and have been struggling with finding a solution to copying & pasting some formulas into a range with a variable end row. I managed to cobble together the below code, which works, but it is inefficient because it pastes the formulas one row at a time. I would like to copy the formulas and then paste them into the entire range at once (instead of row by row). I have to do this function in a few different sheets and ranges so ideally I'd like to create a sub routine to find the last row. What I don't know is 1) how to find the last row 2) how to reference it when I'm selecting the range to paste the formulas into.

The sheet is setup with data in the first column, starting in cell C9, and the formulas are in D8:I8. I need to copy the formulas into the range of D9.I? (with the last row being the last row of data in column C).

I've been working on this for about 5 hours and am going out of my mind. Any help would be appreciated!

Sample of the code I have managed to write that works but isn't efficient:

Range("D8").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Copy
  ActiveCell.Offset(1, -1).Select
Do Until ActiveCell.Value = Empty
  ActiveCell.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
  ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select 
Loop

Answer 1

This solution is a Subroutine to fill a range with values (in this case, formulas) and find the LastRow in a separate Function. There are many ways to do this so feel free to modify it how you please.

First this Subroutine receives the relevant Worksheet, range the formulas are in and the Column letter for the start and end of our destination range.

The Sub uses the Range.AutoFill method to fill the destination range, much the same as if you click the bottom right of a cell with a value and drag up/down/left/right to fill the cells in that direction.

Public Sub AutoFillVariableSizedRangeByRow _
                (ByRef TargetWorkSheet As Worksheet, _
                ByVal TargetValueCellAddress As String, _
                ByVal StartColumn As String, _
                ByVal EndColumn As String)

Dim RangeValuesArray As Variant
Dim TargetValueCell As Range
Dim TargetRange As Range

Set TargetValueCell = TargetWorkSheet.Range(TargetValueCellAddress)
Set TargetRange = TargetWorkSheet.Range(StartColumn & Right(Mid(TargetValueCellAddress, 4), 1) & ":" & _
    EndColumn & LastRow(TargetWorkSheet, "C"))

TargetValueCell.AutoFill TargetRange

End Sub

The LastRow is found by a separate function, which is well explained already in many places on the net, including another answer to this question.

Public Function LastRow(ByRef TargetSheet As Worksheet, ByVal TargetColumnLetter As String) As Long

LastRow = TargetSheet.Cells(Rows.Count, TargetColumnLetter).End(xlUp).Row

End Function

To write the LastRow function with excel references (not user defined variables), it would look like:
Sheet1.Cells(Rows.Count, "C").End(xlUp).Row

To call the sub it could look something like:

Private Sub myProcedure()

AutoFillVariableSizedRangeByRow ThisWorkbook.Sheets("Sheet1"), "D1:I1", "D", "I"

End Sub

In the above, ThisWorkbook.Sheets("Sheet1") is TargetWorkSheet and "D1:I1" is TargetValueCellAddress, "D" and "I" are the start and end columns of our destination range respectively.

---

In this example, I've put values 1 to 20 down column C and the formula =$C1*$C1 in row 1 of columns D to I, all on Sheet1.

And here is the output after running AutoFillVariableSizedRangeByRow Sheet1, "D1:I1", "D", "I":

As example, the formula across row 8 is =$C8*$C8 and row 20 is =$C20*$C20.

Answer 2

The classic way to find the last used row is shown below. Call the function like Debug.Print LastRow or, directly in the Immediate Window, with ? LastRow

Function LastRow() As Long

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(XlUp).Row
    End with
End Function

Observe that both, the .Rows.Count and the result are taken from the ActiveSheet and that the measure is taken in column "A". (You can replace the name "A" with the number 1 in the above formula). If you want to develop the function, pass both the sheet and the column to it as arguments.

.Cells(.Rows.Count, "A") defines the cell A1400000 (or thereabouts), the last cell in the column. Then the function looks for the first occupied cell above that, meaning that if A1 and A10 are in use and A2:A9 are blank, the function will return 10. It's important to understand that .Cells(.Rows.Count, "A").End(XlUp) is a range object, a cell, of which the .Row property holds the number of the row where that range is located.

Now, if you want to define a range D9:I? you might do it like this, setting the range by defining its first and last cell. Observe the 4 leading periods. Each one stands for the object in the With statement, in this case ActiveSheet.

With ActiveSheet
    Set MyRange = .Range(.Cells(9, "D"), .Cells(.Rows.Count, "I").End(xlUp))
End With

But that would take the measure for the last used cell in column I. Often it's the first column on the left that is longer than the last column in the required range. In that case you might use code as shown below.

With ActiveSheet
    Set MyRange = .Range(.Cells(9, "D"), .Cells(.Rows.Count, "D").End(xlUp))
End With
With MyRange
    Set MyRange = .Resize(.Rows.Count, 9)
End With

The code first sets the range for column D only, presuming that column D is the longest one, and then expands it to include 9 columns. Observe the .RowsCount refers to the ActiveSheet in the first With block and to MyRange in the second.

Of course, you could achieve a similar result with this code which calls the LastRow function (which measures the last row in column A):-

With ActiveSheet
    Set MyRange = .Range(.Cells(9, "D"), .Cells(LastRow, "I"))
End With