I wanted a vba code to create a new row with all the functionalities of the previous rows.
If ossible please help
I wanted a vba code to create a new row with all the functionalities of the previous rows.
If ossible please help
This might do the job.
Sub InsertRowBelow()
Dim Rng As Range
Dim R As Long
R = Selection.Row
With ActiveSheet
Set Rng = .Range(.Cells(R, 1), .Cells(R, .UsedRange.Columns.Count))
.Rows(R + 1).Insert
End With
With Rng
.Copy Destination:=.Cells(1).Offset(1)
On Error Resume Next ' if no constants were found
.Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
Application.CutCopyMode = False
End Sub
The code will insert a row below the selected row (first selected row if many are selected). It will then copy everything from the selected row to the new row. Note that this action will fail if there are vertically merged cells in the selected row. Finally, it will remove all values copied from the original, but keep all formulas.
You can link this code to a button on your sheet or, as I usually do it, link it to the double-click event on a particular cell, such as the first empty cell at the bottom of column A.
I used the following macro last year to achieve the same result.
Sub Add_Row()
If MsgBox("Is there more than one blank row at bottom ?", vbYesNo + vbExclamation + vbDefaultButton1, _
"Add a new row ?") = vbYes Then Exit Sub
ActiveSheet.Unprotect ' required for macro to run
' find the last row with data
Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
ActiveCell.Offset(1).Select ' makes the first empty cell (below the last cell with data) the active cell
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12)).Select ' cells A thru L of first blank row
Selection.Copy 'copies cells A thru L of first blank row
ActiveCell.Offset(1).Select ' makes the cell below the active cell
ActiveSheet.Paste ' pastes the copied A thru L cells
Application.CutCopyMode = False ' exits cut/copy mode
ActiveCell.Offset(-1).Select ' makes the cell above the active cell
ActiveCell.Comment.Delete ' removes comment from cell
ActiveCell.Validation.Delete ' removes data validation rule from cell
ActiveSheet.Protect ' to prevent sheet formulas from becoming corrupted
Application.SendKeys "{NUMLOCK}" ' keeps NUMLOCK turned ON
Application.SendKeys "%H%" ' this selects the HOME tab of the ribbon
End Sub