Sub Check_Usedrange()
Dim lngLastRow As Long, lngLastCol As Long

On Error Resume Next
lngLastRow = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
.Rows(lngLastRow).Copy
.Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats
.Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas
End With
End Sub

No to the second part, not necessary for this code to work.

--
HTH

Bob Phillips

"Sige" wrote in message
oups.com...
Hi There,

Underneath code checks out the my last cell in the UsedRange. (Thanks
Rob van Gelder!)

2 Issues:

1.I would like to copy the Formulas and Formatting (not the values)of
this last (used) row into the next.
(so the UsedRange gets expanded with 1 row- a copy of the now
one-but-last)

2.It might be necessary to "reset" the Used Range, right?
(and how to "reset" this if there are merged ranges in play?)


Sub Check_Usedrange()
Dim lngLastRow As Long, lngLastCol As Long

On Error Resume Next
lngLastRow = 1: lngLastCol = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
lngLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
.Cells(lngLastRow, lngLastCol).Select
End With
End Sub


Hope you can help me again,
Cheers Sige