detecting row or column insertion & deletion

[arun]

Hi,

How can i detect a new row or column being inserted or
deleted?

When i use the WorkSheet_Change event, i do get range
sizes that indicate if the particular range in question is a row
or column in full.

However i get the same range if the entire row/column is
selected and cleared too. How can i distinguish between
the different events of insertion/deletion/clearing ?

thanks in advance,
arun

 

[Bernie Deitrick]

--
HTH,
Bernie
MS Excel MVP

 

[arun]

--

-- ?
Please, does that mean this is not possible ?

 

[Bernie Deitrick]

Arun,

Sorry about the last blank post.

The answer is that there is no event based on row/column insertion/deletion.

One way is a fairly convoluted approach: select the first two rows of your
used range, and enter the formula =COLUMN() in each cell, then convert the
first row to values.

Then use the worksheet calculate event below.

A similar approach can be used for row insertion/deletion.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim GaveMsg As Boolean
GaveMsg = False
Application.EnableEvents = False
For Each myCell In Intersect(Range("B1:IV1"), ActiveSheet.UsedRange)
If (Not GaveMsg) And (myCell.Value <> myCell(2).Value) Then
If myCell.Value > myCell(2).Value Then
MsgBox "Column deletion"
Else
MsgBox "Column insertion"
End If
GaveMsg = True
End If
myCell.Value = myCell.Column
myCell(2).Formula = "=COLUMN()"
Next myCell
Application.EnableEvents = True
End Sub

 

[arun]

The answer is that there is no event based on row/column

insertion/deletion.

Well isnt it even possible to use some property or state of the range or
worksheet object returned with the SheetChange event to recognize that
this is what has happened ?

Thank you,
arun

 

[Bernie Deitrick]

Arun,

No, there is no way native to Excel to determine when a column or row has
been added or deleted.

There are as many ways around this as there are VBA experts: my last post
shows a solution that works. You can use the SheetChange event as well -
BUT you STILL need a way to determine if the row or column was added or
deleted.

HTH,
Bernie
MS Excel MVP