Deleting rows

[Jeff]

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

 

[Mike H]

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

 

[Chip Pearson]

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)