delete rows

  • Thread starter Thread starter dd
  • Start date Start date
D

dd

i would like to run a macro that does the following.
start at row 2
if L2 and m2 and n2 are all zeros then delete row
if not then go to next row and do the same check.

the macro should end once the "L" field is blank.

let me know if more info is need

thanks

--
 
One way:

Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim DelRng As Range

Set wks = ActiveSheet
With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row

For iRow = FirstRow To LastRow
If Application.IsNumber(.Cells(iRow, "L").Value) _
And Application.IsNumber(.Cells(iRow, "M").Value) _
And Application.IsNumber(.Cells(iRow, "N").Value) Then
If Application.Max(.Cells(iRow, "L").Resize(1, 3)) = 0 _
And Application.Min(.Cells(iRow, "L").Resize(1, 3)) = 0 Then
If DelRng Is Nothing Then
Set DelRng = .Cells(iRow, "L")
Else
Set DelRng = Union(.Cells(iRow, "L"), DelRng)
End If
End If
End If
Next iRow
End With

If DelRng Is Nothing Then
'do nothing
Else
DelRng.EntireRow.Delete
End If
End Sub

It just checks to see if each of those cells is a number (application.isnumber
is more stringent than VBA's isnumeric). Then if they are all numbers, it
checks to see if the max = min = 0. If yes, then delete that row.

It starts at row 2 and finishes with the last used cell in column L (could be
slightly different than what you asked).
 
Back
Top