Aussiebear
12-10-2011, 03:20 PM
I guess there's many ways to skin a cat as evidenced by the three following examples, but which way is considered better and why?
Example 1 Using Defined fomula then Autofill
Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet1
.Range("B2").Formula = "=H1-G1"
.Range("B2").AutoFill .Range("B2:B" & lastrow)
End With
Application.ScreenUpdating = True
End Sub
Example 2 Defines a formula in the range
Sub Fill_Down()
Dim lngLastRow As Long
lnglastrow = cells(rows.count, "A").end(xlup).row
Application.ScreenUpdating = False
Range("B4:B" & lnglastrow).formula = "=H1-G1"
Application.ScreenUpdating = True
End Sub
Example 3 I'm guessing assumes the formula is already in cell B2?
Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
.Range("B2:B").AutoFill Destination:=.Range("B2:B" & lastrow)
End With Application.ScreenUpdating = True
End Sub
Example 1 Using Defined fomula then Autofill
Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet1
.Range("B2").Formula = "=H1-G1"
.Range("B2").AutoFill .Range("B2:B" & lastrow)
End With
Application.ScreenUpdating = True
End Sub
Example 2 Defines a formula in the range
Sub Fill_Down()
Dim lngLastRow As Long
lnglastrow = cells(rows.count, "A").end(xlup).row
Application.ScreenUpdating = False
Range("B4:B" & lnglastrow).formula = "=H1-G1"
Application.ScreenUpdating = True
End Sub
Example 3 I'm guessing assumes the formula is already in cell B2?
Sub Fill_Down()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
.Range("B2:B").AutoFill Destination:=.Range("B2:B" & lastrow)
End With Application.ScreenUpdating = True
End Sub