Copy Last row UsedRange to next row
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 |
Copy Last row UsedRange to next row
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 |
Copy Last row UsedRange to next row
Hi Bob,
Thx a lot ... it works almost fine! i.e. also constants are pasted! & I would prefer not. Sige |
Copy Last row UsedRange to next row
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" wrote in message oups.com... Hi Bob, Thx a lot ... it works almost fine! i.e. also constants are pasted! & I would prefer not. Sige |
Copy Last row UsedRange to next row
Just Perfect Bob !!! Thx again, Sige |
Copy Last row UsedRange to next row
Bob,
Is there a way to paste also the Cells-Locked status? BRG Sige |
Copy Last row UsedRange to next row
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 |
All times are GMT +1. The time now is 10:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com