CODEWITHSUNDEEP
excelvba
corbfon
corbfon

Reputation: 100

How to tell if user is deleting or inserting rows - Excel VBA

I am in a situation where the columns on one Worksheet need to track with the rows on another Worksheet. So, if a user deletes a column (which they must be allowed to do), I need the corresponding row in another worksheet to be deleted. That works fine, and the code below accomplishes the task.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Target.EntireRow.Address Then
        Debug.Print Target.Address
        Sheet2.Range("A1").Offset(, Target.Row - 1).EntireColumn.Delete
    End If
End Sub

However, this same code runs when a user inserts a row, while in fact I need to add a row to Sheet2 in that case. How can I tell if the user is inserting or deleting when a row action is taken?

Upvotes: 0

Views: 929

Answers (2)

Ravi
Ravi

Reputation: 2568

I got it working like this, usually when a row/s is added or deleted the target address will be something like $ROW_NUMBER:$ROW_NUMBER.

Example

if 6th row is deleted or inserted the Target.Address will be $6:$6.

if 6th to 8th rows are deleted or inserted the Target.Address will be $6:$8

I wrote this function which worked for me

    Public Function IsRowInsertedOrDeleted(ByVal Target As Range) As Boolean
       Dim RegEx As Object
       Set RegEx = CreateObject("VBScript.RegExp")
    
       With RegEx
          .Pattern = "\$\d+:\$\d+"
          .IgnoreCase = True
       End With
        
       IsRowInsertedOrDeleted= RegEx.test(Target.Address)
    End Function

If it is true just use the Target.Address and delete the rows in another sheet.

Upvotes: 0

Malil
Malil

Reputation: 143

Since I can't add it as a comment yet, I believe this was already answered here

Basically, you would define a range name, and then call that range name on Worksheet_Change. Based on whether the range has moved and which direction, you should be able to determine whether columns have been added or removed, and then process accordingly.

Brettdj's code from the above-linked post (For rows, but easily changable for columns):

You could define a range name such as RowMarker =$A$1000

Private Sub Worksheet_Change(ByVal Target As Range)
Static lngRow As Long
Dim rng1 As Range
Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
If lngRow = 0 Then
lngRow = rng1.Row
    Exit Sub
End If
If rng1.Row = lngRow Then Exit Sub
If rng1.Row < lngRow Then
    MsgBox lngRow - rng1.Row & " rows removed"
Else
    MsgBox rng1.Row - lngRow & " rows added"
End If
lngRow = rng1.Row
End Sub

Upvotes: 4

Related Questions