autofill won't fill until last row

Question

I have a code for inserting formula at some row in a module

Then I add a new module to autofill the formula depending on C column (if C 15 data then it autofill down 15)

The autofill works manually to what I expected, but when I set to Private Sub for Workbook_Open, the autofill doesn't fill the last row (if 15 data, the macro only fill until row 14)

Below is my autofill code

Sub AutoFill()

lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

ThisWorkbook.Sheets(1).Range("A6:B6").AutoFill Destination:=ThisWorkbook.Sheets(1).Range("A6:B" & lr), Type:=xlFillDefault
ThisWorkbook.Sheets(1).Range("D6:S6").AutoFill Destination:=ThisWorkbook.Sheets(1).Range("D6:S" & lr), Type:=xlFillDefault


End Sub

How do I make this code run until the last row when I set it to Private Sub?

Answer 1

Range.AutoFill

• The important difference is that in this solution the .Cells are qualified ws.Cells i.e. ThisWorkbook.Sheets(1).Cells and ThisWorkbook.Sheets(1).Cells(1, 1) to ensure that the correct worksheet is being searched. You can use variables to shorten the lines.

Option Explicit

Sub AutoFill()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)
    
    Dim lrCell As Range
    Set lrCell = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lrCell Is Nothing Then Exit Sub ' empty worksheet
    
    Dim lr As Long: lr = lrCell.Row
    If lr < 7 Then Exit Sub ' no data below
    
    ws.Range("A6:B6").AutoFill Destination:=ws.Range("A6:B" & lr), Type:=xlFillDefault
    ws.Range("D6:S6").AutoFill Destination:=ws.Range("D6:S" & lr), Type:=xlFillDefault

End Sub