Copy Last row UsedRange to next row

[Sige]

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

 

[Bob Phillips]

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.

 

[Sige]

Hi Bob,

Thx a lot ... it works almost fine!

i.e. also constants are pasted! & I would prefer not.

Sige

 

[Bob Phillips]

Try this Then Sige

Sub Check_Usedrange()
Dim lngLastRow As Long, lngLastCol As Long, j 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
For j = 1 To .Cells(lngLastRow + 1,
Columns.Count).End(xlToLeft).Column
If Not .Cells(lngLastRow + 1, j).HasFormula Then
.Cells(lngLastRow + 1, j).ClearContents
End If
Next j
End With
End Sub

 

[Sige]

Just Perfect Bob !!!

Thx again, Sige

 

[Sige]

Bob,

Is there a way to paste also the Cells-Locked status?

BRG Sige

 

[Sige]

Bob,

Is there a way to Paste also the Locked-status of the copied cells?
If my sheet is locked it does not copy the Locked-status of the cellls.

Brgds Sige