Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Last row UsedRange to next row
Just Perfect Bob !!! Thx again, Sige |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Last row UsedRange to next row
Bob,
Is there a way to paste also the Cells-Locked status? BRG Sige |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
usedRange | Excel Programming | |||
Excel 2003 Issue with UsedRange.Copy (code works in Excel 2002) | Excel Programming | |||
UsedRange.Copy copiess ##### instead of text | Excel Programming | |||
UsedRange | Excel Programming | |||
Usedrange | Excel Programming |