Getting Worksheet_Change to Ignore First Row
 

I am using the following code to add formulas to the next row when
data is entered in column A. The formulas are in columns B, C, D, and
E.

Row 8 is a header row.
Data entry starts in cell A9.

It works except for data entered in A9. When data is entered in A9
the header row is copied to B9:E9.

How can I get this code to ignore data entry into cell A9?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then
Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Getting Worksheet_Change to Ignore First Row
 

Brad

Start your code with

If Not Application.Intersect(Range("A9"),Target) is Nothing Then
Exit Sub
End If


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"General Specific" wrote in message
...

I am using the following code to add formulas to the next row when
data is entered in column A. The formulas are in columns B, C, D, and
E.

Row 8 is a header row.
Data entry starts in cell A9.

It works except for data entered in A9. When data is entered in A9
the header row is copied to B9:E9.

How can I get this code to ignore data entry into cell A9?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then
Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Getting Worksheet_Change to Ignore First Row
 

On May 20, 4:41*pm, "Nick Hodge" wrote:
Brad

Start your code with

If Not Application.Intersect(Range("A9"),Target) is Nothing Then
Exit Sub
End If

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"General Specific" wrote in message

...





I am using the following code to add formulas to the next row when
data is entered in column A. *The formulas are in columns B, C, D, and
E.

Row 8 is a header row.
Data entry starts in cell A9.

It works except for data entered in A9. *When data is entered in A9
the header row is copied to B9:E9.

How can I get this code to ignore data entry into cell A9?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* *Dim c As Range, i As Long
* *On Error Resume Next
* *Set c = Intersect(Target, Columns(1))
* *If c Is Nothing Then Exit Sub
* *If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then
Exit Sub
* *i = c.Row
* *Application.EnableEvents = False
* *Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
* *Application.EnableEvents = True
* *On Error GoTo 0
End Sub- Hide quoted text -

- Show quoted text -

Excellent!

Now, this doesn't work if data is pasted, like if 20 numbers are
pasted at once. Why?