Counting deleted rows

  • Thread starter Thread starter sw
  • Start date Start date
S

sw

Is there anyway of counting rows that have been deleted
e.g, if i have a range of data in A1:A1000 and at the
bottom I have =COUNT(A1:A1000)
I then need to now how many rows I have deleted e.g. if I
delete the details in rows 10, 20 and 30 this count should
give me 3.

The problem is that I may also be adding in new rows
within this same area e.g. I may add in 2 new rows between
A1 and A1000.

I'm not even sure if this is a formula or I need vb code -
can anyone help?
 
sw,

The only way to do this is to use a helper column.

Say that you have data in rows 1 to 1000. Insert a new column (let's say
A), and enter 1 into every new cell to match your data. Then you can use
the formula

=1000 - SUM(A:A)

to give you how many rows were deleted.

If you insert rows, either don't enter a 1 in column A to keep track of just
the deletions, or if you want to offset your deletion count with insertions,
enter a 1 in column A of that row to adjust your sum.

HTH,
Bernie
MS Excel MVP
 
Back
Top