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