Macro to delete inserted row

[jnbroek]

The following formula enables me to insert a row whenever I make a new entry in the A column.

            If blnClickedEmpty Then
                With Me.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                    If rngClickedCell.Row + 1 = .Row Then
                        Me.Range("A4:BD4").Copy
                        .Cells.Insert xlDown
                        Application.CutCopyMode = False
                        .Offset(-1).Resize(, 56).SpecialCells(xlConstants).ClearContents
                    End If
                End With
            End If

However, I now need to put in another macro to remove the last inserted row whenever an entry is removed from the A column. I don't know how to write the macro to accomplish this function. I think the above code information may be enough to determine how the new code should be written. Please let me know if there is more information to share in order to solve this problem.

[ranman256]

record a macro,
run the delete
stop the macro,
edit the macro.

remove any text of a hard defined cell reference, like Range("a1") and replace it with ACTIVECELL.

If you are deleting a row, then where it says Rows("4:4"), change to:
r = activecell.row
Rows(r & ":" & r)

[jnbroek]

I'm sorry, I don't know how to code the macro to delete the last row with formulas whenever a value in Column A has been erased or cleared. As it is right now, another new row is created whenever I delete a value from one of the cells on column A. It keeps compounding and I do not want that!

[Winon]

Hello jnbroek,

As it is right now, another new row is created whenever I delete a value from one of the cells on column A.

I think we would need to see a sample of how you have setup your Workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Regards.

[jnbroek]

I apologize. I realize now that a new row does NOT get inserted after a value is cleared from the A column. Instead, what happens is that the row #11 was inserted AFTER I added a value "gg" in the A5 cell (see Before file example). But when I cleared the A5 cell, row #11 still remains there (see After file example).

Would be perfect if that colored row #11 also goes away when the value is removed from the A5 cell.

Every time I add a new value to the A column, a new colored row with formulas gets inserted. But those inserted rows will never go away unless I manually delete them. If I give this protected workbook to a novice user, the Miscellaneous Activities worksheet could end up having rows and rows of colored rows if they keep removing values in the A column.