Determining When a Row/Cell is Inserted or Deleted in Excel VBA

Question

I was trying to figure out how to determine when a row (or column) is inserted or deleted in Excel using VBA during the Worksheet_Change event and came across the topic here. However, the suggested answers given there, didn't fully capture all instances when a row is inserted or deleted on a worksheet including:

While they capture instances of when ENTIRE rows are inserted or deleted, they don't capture instances where only a group of cells are inserted or deleted shifting cells below or above down or up.
While they capture instances of rows inserted or deleted within a USEDRANGE, they don't capture instances where you are inserting outside of the used range (e.g. inserting a row below where you have data entered).

Given the above statements, I searched for more options and could not find any. Then, I came up with my own solution which I'm providing here to help others. I'm also hoping at the same time to get feedback on any flaws or areas of improvement.

Why don't you add this to the original thread rather than as a new q&a? — brettdj, Mar 14 '16 at 02:58

score 2 · Answer 1 · answered Mar 14 '16 at 01:13

The below Excel VBA codes will determine whether or not a user has either inserted or deleted an entire row or group of cells (shifting cells up or down) all within or out of the used range.

Here's the code (could also be converted for columns as well):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells(Target.Row + Target.Rows.Count, Target.Item(1, 1).Column).ID = Target.Address
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Item(1, 1).ID <> "" Then
        MsgBox "deleted row"
    Else
        MsgBox "inserted row"
    End If
    Target.Item(1, 1).ID = ""
    Cells(Target.Row + Target.Rows.Count, Target.Item(1, 1).Column).ID = Target.Address
End Sub

It's very simple actually and uses cell tagging to keep track of what the user actions which you can then use to determine if an insert or delete occurred.

"Wait?!" You might say. "There's a way to tag cells?" Or... "What is cell tagging".

Well, there really isn't a "tag" property for a cell, but you can retrofit the "Cell.ID" property and use it as a tag for a cell. The "Cell.ID" property's original intent is to be used when saving the Excel file as a webform, but it works pretty as a tag as well if your not going to create webforms. The only downside is you cannot save it's value with the workbook when you close and save the file. But, that's okay for this because you don't need to save tag values anyway.

Anyway, I think it works well. But, please let me know if you see any areas for improvement or flaws.

Only issue I can think of if as you click around, it tags the cells continually and leaves a lot of left over trash lying around if you don't end up making a change to a cell. But, this is not saved when you close the workbook and only occurs while the file is open.

Your idea is interesting, however I start with an empty sheet, select a row and touch **Insert**, I get the MsgBox telling me a row was entered......but if I select a row and set all its cells to some value by using **Ctrl** + **Enter**, I get the same message. — Gary's Student, Mar 14 '16 at 01:56
**I don't want to discourage you.** I also have a strong interest in this topic. — Gary's Student, Mar 14 '16 at 02:16

score 2 · Answer 2 · answered Mar 15 '16 at 20:05

Ok I'm back. Still testing, but here is my status so far. It got more complicated because you have to keep track of an additional tag and clean-up after yourself. It's a matter of keeping track of what Excel does to the tagged cells (did they shift down, shift up, etc...). Once that pattern is figured out and fully trapped, then I think this should work. Fingers crossed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call TagCellManager(Target)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurCel As Range, CurRow As Range
    Dim TagCel As Range, TagRow As Range
    Dim NxtCel As Range, NxtRow As Range

    Set CurCel = Target.Item(1, 1)
    Set CurRow = Cells(CurCel.Row, 1)
    Set TagCel = Cells(CurCel.Row + Target.Rows.Count, CurCel.Column)
    Set TagRow = Cells(CurCel.Row + Target.Rows.Count, 1)
    Set NxtCel = TagCel.Offset(Target.Rows.Count, 0)
    Set NxtRow = TagRow.Offset(Target.Rows.Count, 0)

    If TagCel.ID <> "" And TagRow.ID <> "" Then
        'ignore me. i'm entering data
    ElseIf NxtCel.ID <> "" Or NxtRow.ID <> "" Then
        MsgBox "row inserted"
        Set Target = Range(NxtCel.ID)
        Call TagCellManager(Target)
    ElseIf CurCel.ID <> "" Or CurRow.ID <> "" Then
        MsgBox "row deleted"
        Set Target = Range(CurCel.ID)
        Call TagCellManager(Target)
    End If
End Sub

Sub TagCellManager(Target As Range)
    Dim CurCel As Range, CurRow As Range
    Dim TagCel As Range, TagRow As Range
    Dim NxtCel As Range, NxtRow As Range

    Set CurCel = Target.Item(1, 1)
    Set CurRow = Cells(CurCel.Row, 1)
    Set TagCel = Cells(CurCel.Row + Target.Rows.Count, CurCel.Column)
    Set TagRow = Cells(CurCel.Row + Target.Rows.Count, 1)
    Set NxtCel = TagCel.Offset(Target.Rows.Count, 0)
    Set NxtRow = TagRow.Offset(Target.Rows.Count, 0)

    CurCel.ID = ""
    CurRow.ID = ""
    TagCel.ID = Target.Address
    TagRow.ID = Target.Address
    NxtCel.ID = ""
    NxtRow.ID = ""
End Sub

Did it work? If it did, you could accept your own answer and let everyone know that it did. — Rama, May 25 '16 at 08:55

Determining When a Row/Cell is Inserted or Deleted in Excel VBA

2 Answers2