detecting row or column insertion & deletion
 

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

detecting row or column insertion & deletion
 

--
HTH,
Bernie
MS Excel MVP
"arun" wrote in message
...
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

detecting row or column insertion & deletion
 

"Bernie Deitrick" <deitbe @ consumer dot org
wrote in message ...

--

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



HTH,
Bernie
MS Excel MVP
"arun" wrote in message
...
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 ?

detecting row or column insertion & deletion
 

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" wrote in message
...
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

detecting row or column insertion & deletion
 

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...


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

detecting row or column insertion & deletion
 

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

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 ?