Why xl deletes 250+ rows using worksheet_change

[stumanchu]

A simple macro to delete 1 row at a time when the value of a cell
changes now deletes 250+ rows at a time. I only want to delete the 1st
row of a worksheet when there is a change in a cell on the 1st row, then
move the following row in its place.

Private Sub Worksheet_Change(ByVal Target As Range)

Range("1:1").Select
Selection.Delete xlShiftUp
End Sub

Thanks for any help!!

 

[Chip Pearson]

When you change a cell, the Change event is called. This deletes
row 1, which is a change, so Change is called again, which
deletes row 1, which causes Change to be called again, over and
over and over. Change your code to

Application.EnableEvents = False
Range("1:1").Select
Selection.Delete xlShiftUp
Application.EnableEvents = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"stumanchu"
message
news:[email protected]...

 

[stumanchu]

I see!! Thanks a bunch - it works!