CODEWITHSUNDEEP
excelvbaexcel-formula
Ray
Ray

Reputation: 1

I have a VB code to add a row in excel, but it keeps deleting the formulas in all columns

I have some VBA code in excel to add a row when I click a button. The problem is that the formulas are deleted in the rows when I click the "Add Row" button.

The code is this:

Sub AddRow()
    Range("A" & Rows.Count).End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Insert
End Sub

I cannot think of a way to get around this problem. I'm by no means fluent in coding at all. I obtained the code from a youtube video that looked like it would help me with adding rows.

An example of the formulas that I have entered in is:

=IF(ISNUMBER(SEARCH("Cuvette Packaging",$C$105:$C$419)),$B$105:$B$1276,"")

I just can't seem to find out how to get around the code to not delete what I have in all of my columns.

Upvotes: 0

Views: 44

Answers (1)

Skin
Skin

Reputation: 11207

Try something like this ...

Sub AddRow()
    Dim lngLastRow As Long, rngActiveCell As Range

    ' Rename "Sheet1" to the name of your worksheet.
    With ThisWorkbook.Worksheets("Sheet1")
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        ' Get the active cell, we'll make sure we reselect it after the copy operation completes.
        Set rngActiveCell = ActiveCell

        ' Turn screen updating off.
        Application.ScreenUpdating = False

        ' Copy and paste the last row to the row below.
        .Rows(lngLastRow).Copy
        .Rows(lngLastRow + 1).PasteSpecial

        ' Select the previously active cell.
        rngActiveCell.Select

        On Error Resume Next

        ' Clear the cells that do not contain formulas.
        .Rows(lngLastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents

        On Error GoTo 0

        ' Do some clean up work.
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End With
End Sub

There are few other things that may need to be thrown in but go with that as a start.

enter image description here

I hope it helps.

Upvotes: 1

Related Questions