Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
With ActiveSheet.UsedRange
lastrow = .Row + .Rows.Count - 1
.Range(.Cells(lastrow, 1), .Cells(lastrow, .Columns.Count)).Copy
.Cells(lastrow + 1, 1).Select
ActiveSheet.Paste
End With
Option Explicit
Sub SetupNewLine()
' Generic macro to copy last line of the active sheet to
' the (empty) line below (formulas and formatting only).
Dim nLastRow As Long
' Find where to paste
nLastRow = LastRow(ActiveSheet)
' Copy and paste
ActiveSheet.Cells(nLastRow, 1).EntireRow.Copy
Cells(nLastRow + 1, 1).Select
Selection.PasteSpecial Paste:=xlFormats
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
Cells(nLastRow + 1, 1).Select
End Sub
Function LastRow(AWorksheet As Worksheet) As Long
' Generic function to find the last actually used row
' in a worksheet. "UsedRange" is taken for the first
' approximation. Then rows are then tested for the
' presence of actual data with the CountA function.
' Rows that only have formatting, colors, patterns,
' borders, etc. are ignored.
' Returns zero if there is no data in the sheet
' or if there is invalid input.
Dim bCheckingForBadUsedRange As Boolean
If AWorksheet Is Nothing Then
MsgBox "LastRow function called without a valid Worksheet object."
LastRow = 0
Else
With AWorksheet.UsedRange
LastRow = .Rows.Count + .Row - 1
End With
bCheckingForBadUsedRange = True
While bCheckingForBadUsedRange And LastRow > 0
If LastRow > 0 And WorksheetFunction.CountA(Cells(LastRow, 1).EntireRow) = 0 Then
LastRow = LastRow - 1
Else
bCheckingForBadUsedRange = False
End If
Wend
End If
End Function