I need to create a button so that it will create a new row with th existing row properties

0

I wanted a vba code to create a new row with all the functionalities of the previous rows.

If ossible please help

Answer
Discuss

Discussion

Do you mean to copy/paste them? What functionalities are you talking about - formulas and functions, conditional formatting, data validation, security settings (locking), etc?
don (rep: 1247) Jul 28, '17 at 7:11 am
Add to Discussion

Answers

0

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.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login