insert row EVENT

[RSXchin]

If I insert a row at row 10 on sheet 1, how do I automatically insert a row at row 10 on sheet 2?

 

[Jonmo1]

If you select both sheets at the same time...

Select Sheet1
Press and Hold the CTRL Key
Select Sheet2
Release the CTRL Key
Insert Row 10

Then right click either sheet's tab and choose "Ungroup Sheets"


Hope that helps.

 

[RSXchin]

I was talking about a macro event. Here is how I got around it

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 100 Then
        MsgBox "row"
    End If
End Sub

 

[Jonmo1]

Unfortunately there is no event that captures Insert Row...Persay...

The Change event IS triggered by inserting/deleting Rows.
However, it doesn't offer any method of knowing if it was triggered by row insertion/deletion.
And also no way of knowing WHICH row was inserted/deleted.


Here's a trick you might use,
Which is by no means foolproof.

Put any value (like "Last") in column A (or any column of your choice)
at a row FAR below your actual last used row.
Make note of the row you put it in.

Private Sub Worksheet_Change(ByVal Target As Range)
lr = Cells(Rows.Count, "A").End(xlUp).Row
MyRow = 500 'This is the row you put "Last" in
Select Case lr
    Case Is > MyRow
        If Target.Columns.Count = Columns.Count Then
            MsgBox "A row was inserted at Row # " & Target.Row
        End If
    Case Is < MyRow
        If Target.Columns.Count = Columns.Count Then
            MsgBox "Row # " & Target.Row & " Was Deleted"
        End If
    Case Else
End Select
End Sub

 

[RSXchin]

I like that Jonmo. I would add in a

Case Is = MyRow
    If Target.Columns.Count = Columns.Count Then
        MsgBox "A row cleared but not deleted at # " & Target.Row
    End If

is that good?

 

[Jonmo1]

Not bad, but it wasn't necessarily cleared...
It might have had values added into the row...
Perhaps test if target.value = ""

 

[RSXchin]

if you are changing a value, then the target.columns.count CAN'T be columns.count.

edit:.... unless it was a copy-paste

 

[Jonmo1]

You can change multiple cells at a single time...

Highlight any row.
Type any value
Hold the CTRL Key
Press Enter


Discovering flaw in my previous post...

Perhaps test if target.value = ""
would have to be
Target(1).Value = ""

 

[RSXchin]

0.0 I never knew that!!!!!
That'll be helpful.