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.

--
HTH

Bob Phillips

"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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


--
HTH

Bob Phillips

"Sige" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Thx a lot ... it works almost fine!
>
> i.e. also constants are pasted! & I would prefer not.
>
> Sige
>

[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