Deleting rows
 

I have a spreadsheet that is about 65000 rows. I need to create a macro that
will search column D for any number that is negative. I then need the the
row with the negative number and the row above it deleted.
Any help would be great.

Thanks

Deleting rows
 

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub delete_Me()
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
For X = lastrow To 1 Step -1
If Cells(X, 4).Value < 0 Then
Rows(X).Delete
Rows(X - 1).Delete
End If
Next
End Sub

Mike

"Jeff" wrote:

I have a spreadsheet that is about 65000 rows. I need to create a macro that
will search column D for any number that is negative. I then need the the
row with the negative number and the row above it deleted.
Any help would be great.

Thanks

Deleting rows
 

Try the following code:



Sub AAA()
Dim StartRow As Long
Dim EndRow As Long
Dim DeleteThese As Range
Dim WS As Worksheet
Dim RowNdx As Long
Set WS = Worksheets("Sheet1")
With WS
EndRow = .Cells(.Rows.Count, "D").End(xlUp).Row
StartRow = 1
For RowNdx = EndRow To StartRow + 1 Step -1
If .Cells(RowNdx, "D").Value < 0 Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Rows(RowNdx)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(RowNdx))
End If
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(RowNdx -
1))
End If
Next RowNdx
End With
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End Sub



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 28 Aug 2009 09:58:01 -0700, Jeff
wrote:

I have a spreadsheet that is about 65000 rows. I need to create a macro that
will search column D for any number that is negative. I then need the the
row with the negative number and the row above it deleted.
Any help would be great.

Thanks