Here is some code which I think will do what you require. The second routine
does fire on every change but does not do anything unless the number of rows
has changed. I don't think the user will notice the time delay. There is no
VB trap for inserting or deleeting rows. If you double click on a worksheet
in the VB window, and then select Worksheet in the left hand drop down, the
right had drop down lists the events which can be detected. HTH

Public NrowsSt As Integer
Private Sub Workbook_Open()
NrowsSt = Range("MyRange").Rows.Count
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.UsedRange ' This row is needed to get correct answer when
rows are deleted.
Nrows = Range("MyRange").Rows.Count
NSt = ThisWorkbook.NrowsSt
If Nrows < NSt Then
If Nrows NSt Then
MsgBox "No of rows inserted =" & Nrows - NSt
Else
MsgBox "No of rows deleted =" & NSt - Nrows
End If
ThisWorkbook.NrowsSt = Nrows
End If
End Sub

"ML0940" wrote:

Hey Gleam,

Your code works well but I did forget to tell you that I am only concerned
with the rows in some named ranges.

So, I still definetely see the value in capturing the number of rows on
Workbook Open but again, that is for used ranges, not nec. named ranges.

Also, is there a way to only have the code fire in the event that a row is
deleted or inserted?

The code is firing with every change

Thank you,

Mark

"Gleam" wrote:

ML

I'm not sure how the number of rows can be -1. I think the minimum will in
general be 1, but may sometimes be 0.

Also I see that Rng1 is set to the start of a resize. When used the resize
is not specified. I woud have expected something like Rng1(1,5).rows.count

Looking back, I think you may want to count the number of orws when the
workbook is opened and then to count again every time a change is made to see
if the total number of rows has changed.

Under Microsoft Excel Object you need a module soemthing like
Public NrowsSt
Private Sub Workbook_Open()
NrowsSt=activeworkbook.sheets("Sheet1").usedrange. rows.count

The under the sheet you need
Private Sub Worksheet_Change(ByVal Target As Range)
if activeworkbook.sheets("Sheet1").usedrange.rows.cou nt < NrowsSt then
....
NrowsSt=activeworkbook.sheets("Sheet1").usedrange. rows.count
endif

HTH


"ML" wrote:


Hi Gleam
I appreciate all of the methods

Of all that you have given me; I think that
Nrows=activesheet.usedrange.rows.count

Is the closest to what I need

I want to say
If NamedRange.rows.count = +1 Then
'Code
If NamedRange.rows.count = -1 Then
'Code
End If
End If

I have tried something similar

Dim Rng1 As Range

Set Rng1 = Range("Sh1bills").Resize

If Rng1.Rows.Count + 1 Then
MsgBox "Row Added"
MsgBox "Number of Rows = " & Rng1.Rows.Count
If Rng1.Rows.Count - 1 Then
MsgBox "Row Deleted"
Exit Sub
End If
End If


This is VERY close to what I want however, the -1 is not working quite how I
want
Any ideas?

Thank you again

Mark



"Gleam" wrote:

Please Ignore the bit about not working when deleting rows. I wrote that and
then thought of a way that might work - and it did and so I included it and
then forgot to delete the end bit!

"Gleam" wrote:

There are three ways that I know to count rows. The one I would probably use is
Nrows=activecell.specialcells(xlLastcell).row
This is similar to doing control end on a spread sheet.

Number 2 is
Nrows=activesheet.usedrange.rows.count
This will give the same answer if there are no blank rows at the start. If
rows 1 and 2 are blank then it will give an answer 2 less than the first
method.

Number 3 is
Nrows = range("B47").currentregion.rows.count
This will give the number of rows in the continuous region around cell B47

To dynamically link to changes on the worksheet you need a macro on the
sheet. In the VBA editor please double click on the sheet and try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.UsedRange ' This line is needed to get correct answer when
rows
' are deleted.
MsgBox "No of used rows =" & ActiveCell.SpecialCells(xlLastCell).Row
End Sub

This works for inserting rows.
It doesn't work if the user deletes rows, unless the user also saves the
file! (in Excel 2003)

"ML" wrote:

Hi Susan
I am primarily an AutoCAD-VBA programmer, however I dablle with Excel on
occasion, so please excuse me if I am not a guru either :)

OK, let me think of this in pseudo code:

We actually have a counter; it is The number of used rows in a given range.
So, this part does not seem that difficult pseudo wise.
If user inserts a row within this range; it is rowinsert + 1

The way I see it is, if The formulas can adjust dynaically in worksheet
based on an insert event, then macros would be able to also.
This would likely require a loop an an array of some sort.

Mark

"Susan" wrote:

even if the code were based on a worksheet_change, the worksheet
wouldn't necessarily know WHAT had changed. the only way i can see to
do this (non-guru person here) would be to have a counter somewhere on
a hidden sheet or in a hidden column that kept track of how many rows
you were working with and could thereby track whether or not that
number had changed.......
and even then you'd have to have the original number stored also so it
could compare them. unless it's a constant sized spreadsheet that is
supposed to never get any larger & you're looking for whether or not a
user has slipped an extra row in there.
just ideas
:)
susan



On Nov 28, 12:07 pm, ML wrote:
Hi Gleam

I am not sure precisely how I am going to approach the code yet.
If I had a Boolean function that is true when a row is inserted, I can
possibly work it out from there.

I am trying to figure out how to change some code dynamically based on if a
row is inserted

Mark



"Gleam" wrote:
Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:

Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark- Hide quoted text -

- Show quoted text -