Looking for a way to Autofill Using a Variable Range

Question

I'm looking for a way to autofill my formula down to the last row in the dataset (which is variable) using a range which is also variable. I have highlighted my issue below at the bottom.

Here is the code that I have now:

Sub MissingData()
    Dim LastRow As Long
    Dim LastCol As Long

    Set ws = Worksheets("Insert Data")

    With ws
        Last Row = .Cells(.Rows.Count, 1).End(xlUp).Row
        Last Col = .Cells(1, .Columns.Count).End(xlToLeft).Column

        'Inserting Column Header next to the last column in the data set in row 1"
        .Cells(1, LastCol + 1).Value = "Header"

        'Inserting Formula next ot the last column in the data set in row 2"
        .Cells(2, LastCol + 1).Formula = "=iferror(AJ2,""YES"")"
    End With

    Dim FoundCell As Range

    'Looking for the Last Row in the Dataset"
    'Column A:A will always be populated with data and will be the driver
    'for how many rows are in the data set"
    LR = Worksheets("Insert Data").Range("A:A").End(xlDown).Row

    With ws
        'I set this and then called it using select because my range above
        'and the location of this cell could be variable"
        Set FoundCell = .Cells(2, LastCol + 1)
        FoundCell.Select

        'Here lies my issue.  Using this syntax the formula is filled all the way
        'to the last row available in Excel which is like 1 million something.
        'I just need it filled to the last now that i set above"
        Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown))
    End With
End Sub

[Use `xlUp` to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jan 06 '21 at 15:22

score 2 · Answer 1 · answered Jan 06 '21 at 15:35

A better alternative to AutoFill is to enter the formula in the entire range in one go. Is this what you are trying?

Option Explicit

Sub MissingData()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim ws As Worksheet
    Dim LastColName As String
    
    Set ws = Worksheets("Insert Data")

    With ws
        '~~> Find last row
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        '~~> Find last column and add 1 to it
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1

        '~~> Get Column name from column number
        ' https://stackoverflow.com/questions/10106465/excel-column-number-from-column-name
        LastColName = Split(.Cells(, LastCol).Address, "$")(1)
        
        '~~> Add header
        .Range(LastColName & 1).Value = "Header"
        
        '~~> Add the formula in the entire range in ONE GO
        ' Example: Range("D2:D" & LastRow).Formula = "=IFERROR(AJ2,""YES"")"
        .Range(LastColName & 2 & ":" & LastColName & LastRow).Formula = "=IFERROR(AJ2,""YES"")"
    End With
End Sub

Worked to perfection. I was making it harder on my self than needed :) — Bfox1220, Jan 06 '21 at 16:11

Looking for a way to Autofill Using a Variable Range

1 Answers1