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?
CodePudding user response:
Range.AutoFill
- The important difference is that in this solution the
.Cells
are qualifiedws.Cells
i.e.ThisWorkbook.Sheets(1).Cells
andThisWorkbook.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