Just cheked that only 1 of the code is working ok - this one :
Private Sub Worksheet_Change(ByVal Target As Range)
.....

"ML0940" skrev:


Hi Ex
Thank you for the reply;
Let me see if I have this correct.
You are saying in another sheet, other then the one I will be working on; go
to Cell A1 and actually type the number (Integer) into that cell, that
represents the number (count) of rows

YES

Then the code will comapre the named range to that Integer?
YES
Also, I think you posted the code twice, correct? are they both the same?

NO they are not the same, well not first line anyway - but as i say 1 is ok

I have (named range) Rng1 on Worksheet 1 and I want to do some comparisons
to that range with other named ranges. If something happens to rng1, then do
something with the other ranges

So, may be as Rng1 is being modified, I can have that Integer in A1 update;

IF u insert or delete Rows in Rng1, then A1 is updated by my code

what do you think? I am not sure I like this method because any number of
ranges (3 named so far) can be changed, so there is really no constant value
that I want to committ to.

Well Rng1 allways set equal to sheet? A1

I dont know any other way to check for insert/delete rows.


Mark

"excelent" wrote:

ok here is modifyed code,
Define name Rng1
in a second sheet (in my code Sheet2)
put same number in A1 as rows in Rng1 (u can hide this sheet)

But still the code have to be in sheet1-code-module


Private Sub Worksheet_Change(ByVal Target As Range)
If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo ' Row delete is canceled
Else
[Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and
Sheet2 A1 set to new rows in Rng1
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo ' Row delete is canceled
Else
[Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and
Sheet2 A1 set to new rows in Rng1
End If
End If
End Sub




"ML" skrev:


Hi,
I apoligize; I should have mentioned that I am only concerned with rows
within specific named ranges.

Also, I have some code in the change event; however I only want the code to
fire "if" I insert or delete a row but the code is firing with every change.
???

Here is what I have:

Dim Rng1 As Range

Set Rng1 = Range("Sh1billsW")

If Rng1.Rows.Count + 1 Then
'I will be putting different code in here when I am sure this is fine
MsgBox "Row count has changed"
MsgBox "Number of Rows = " & Rng1.Rows.Count
Rng1.Rows.Select
End If

Thank you.

Mark




"excelent" wrote:

a little change to handle deleting more than 1 row

Private Sub Worksheet_Change(ByVal Target As Range)
If [Ark1!RowLast].Value < 65536 Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo
Else
[Ark1!RowLast].Cut Destination:=Range("A65536")
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [Ark1!RowLast].Value < 65536 Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo
Else
[Ark1!RowLast].Cut Destination:=Range("A65536")
End If
End If
End Sub



"excelent" skrev:

no. but maby a workaround :

If u have a value somwhere in lasteow, Excel automatic create an error
so my code only take care of deleting rows.

First put this formula in cell A65536
=row()

select cell A65536
name this cell as RowLast

Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname)

Private Sub Worksheet_Change(ByVal Target As Range)
If [Ark1!RowLast].Value < 65536 Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo
Else
Range("A65535").Cut Destination:=Range("A65536")
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [Ark1!RowLast].Value < 65536 Then
If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then
Application.Undo
Else
Range("A65535").Cut Destination:=Range("A65536")
End If
End If
End Sub


"ML" skrev:


Hi I was wondering if there is a function out there that will allow me to
catch a row added and a row deleted event.

If a row is deleted then
code
If a row is inserted Then
code
End if
End if

Anyone have something like this?

Thank you,
Mark