detecting row or col insertion & deletion
 

Hi,

This is a follow up from a previous thread,


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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:


Well, i seem to have hit on a VBA way around this like you said but iam
not sure if this is ok - it is based on UsedRange.

Basically, i track UsedRange all the time and keep updating the range
address returned by UsedRange. Whenever a SheetChange event is caused
due to a column insertion or deletion, the dimensions of UsedRange address
either swell or reduce, compared to the last measured address.

To confirm it all you need is a MsgBox with UsedRange.Address in the
SheetChange event of the workbook

Has anyone tried this before ?

The only problem that i found was that if i enter data only in the IV column
then usedrange is returned as $4:$2323 (I enter data only in 4th cell &
2323rd cell).

Is this a valid address (where else or how else can i see it)? or is this a
bug with UsedRange ? I have seen so many bugs reported with UsedRange
but most of them seem to be regarding extra rows reported, which is not
an issue for me. Is there any other probelm with this ?

thanks
arun