SOS:Know Deleted/Inserted Range in Excel 97/2000

  • Thread starter Thread starter Von Shean
  • Start date Start date
V

Von Shean

SheetCalculate event is fired whenever row/col insert/delete is done.
And only calculate function 'Workbook_SheetCalculate(ByVal Sh As Object)' is
called in Excel 97. The Object parameter has a Worksheet object.
Is there any way i can know what range was deleted/inserted.
 
I've done a Google search for a method:
http://groups.google.co.nz/groups?h...m=a78501c20cb4$eb1406c0$37ef2ecf@TKMSFTNGXA13

---snip---

From: Harlan Grove ([email protected])
Subject: Re: Capture an Insert Row Worksheet event
Newsgroups: microsoft.public.excel.worksheet.functions
Date: 2002-06-06 10:28:52 PST


D. Guzman said:
Sorry forgot to mention that the problem was in Excel
2000. So far deleting a row triggers the Worksheet_Change
event but it doesn't trigger anything when I Insert a
row. I really need to be able to capture that event some
how. Any ideas?
Click to expand...

Worksheet_Calculate and Workbook_SheetCalculate are both triggered by
inserting or deleting rows and columns. If you define names referring to a
range covering the original used range, say CurrentRng referring to
$A$1:$CZ$5000, and PreviousRng storing the address of CurrentRng, then the
event handler when triggered could compare the address of CurrentRng to the
stored PreviousRng to see if/how it had changed. The event handler would
update PreviousRng just before exiting. Combine that with checking the
location of ActiveCell, and you could determine where the rows or columns
were inserted. Caveat: this REQUIRES automatic recalculation - the Calculate
event handlers are triggered when recalc actually occurs.

---snip---
 
Back
Top