Why xl deletes 250+ rows using worksheet_change
 

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!!


--
stumanchu
------------------------------------------------------------------------
stumanchu's Profile: http://www.excelforum.com/member.php...o&userid=33793
View this thread: http://www.excelforum.com/showthread...hreadid=535662

Why xl deletes 250+ rows using worksheet_change
 

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"
wrote in
message
...

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!!


--
stumanchu
------------------------------------------------------------------------
stumanchu's Profile:
http://www.excelforum.com/member.php...o&userid=33793
View this thread:
http://www.excelforum.com/showthread...hreadid=535662

Why xl deletes 250+ rows using worksheet_change
 

I see!! Thanks a bunch - it works!


--
stumanchu
------------------------------------------------------------------------
stumanchu's Profile: http://www.excelforum.com/member.php...o&userid=33793
View this thread: http://www.excelforum.com/showthread...hreadid=535662