Insert new row after last used row and copy formula and format from above

Question

I want a macro that will find the last row with data, then insert a new row beneath and copy the format and formula from above (formula is in column F). The code I have inserts a new row in the same spot each time. Is this possible?

Here's what I have:

    Sub AddNewRow()
'
' AddNewRow Macro
'

'
    Rows("37:37").Select
    ActiveSheet.Unprotect
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F36").Select
    Selection.AutoFill Destination:=Range("F36:F37"), Type:=xlFillDefault
    Range("F36:F37").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:=True _
        , AllowFiltering:=True
End Sub

Answer 1

You just need to create variables to find the last and first empty rows and use that instead:

Sub AddNewRow()


' AddNewRow Macro

Dim LastRw As Long, EmptyRw As Long

EmptyRw = WorksheetFunction.CountA(Range("F:F")) + 1
LastRw = WorksheetFunction.CountA(Range("F:F"))

Cells(EmptyRw, 6).Select
ActiveSheet.Unprotect
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(LastRw, 6).Select
Selection.AutoFill Destination:=Range(Cells(LastRw, 6), Cells(EmptyRw, 6)), Type:=xlFillDefault
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:=True _
    , AllowFiltering:=True

End Sub

Answer 2

Could use the CurrentRegion to find last row and fill down from there...

Sub AddNewRow()

ActiveSheet.Unprotect
ActiveSheet.Range("A1").CurrentRegion.Offset(ActiveSheet.Range("A1").CurrentRegion.Rows.Count - 1).Resize(2).FillDown
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:=True _
    , AllowFiltering:=True

End Sub

Would need to change the Range("A1") if worksheet contents does not start in or next to cell A1