You can detect last row at worksheet by using below code and also you may use it to create dynamic defined range.
Here is the code:
Sub AutoFomulate() 'Detecting last row of Column A at worksheet named Example. Sheets("Example").Select Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'You may create autofill formula by using LastRow like below. Range("B2:B" & LastRow).FormulaR1C1 = "=UPPER(RC[-1])" End Sub
You can download running sample from below link.
Download example file