Ctrl+select in VBA

[01's]

Hi,

I need to select all cells in a range which have 0 or null value and then delete entirerow.
I tried below code but it deletes some rows and eliminates others. Can you pls suggest something.

For Each c In ActiveSheet.Range("E2:E" & Lastrow).Cells
If c.Value = 0 Or c.Value = Null Then
Selection.EntireRow.Delete
c.Select
End If
Next

 

[Sektor]

Sub DeleteAllRows()
    For i = lastRow To 2 Step -1
       If Cells(i, "E") = 0 Or IsEmpty(Cells(i, "E")) Then Cells(i, 1).EntireRow.Delete
    Next
End Sub

P.S. When deleting rows, ALWAYS go from down to top. Otherwise you'll loose row/iterator correspondence.

 

[Richard Schollar]

Hi

Dim i As Long
For i = LastRow to 2 Step -1
  If Range("E" & i).Value = 0 Or IsEmpty(Range("E" & i).Value) Then
      Range("E" & i).EntireRow.Delete
  End If
Next i

 

[VoG]

You need to loop backwards when deleting rows. Try

For i = lastrow To 2 Step -1
    If Range("E" & i).Value = 0 Or Range("E" & i).Value = "" Then Rows(i).Delete
Next i

 

[JamesW]

Hi,

You need to loop backwards. Try something like

Option Explicit
Dim lRow As Long, i As Long
Sub test()
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 5).Value = 0 Or Cells(i, 5).Value = Null Then Cells(i, 5).EntireRow.Delete
    Next i
End Sub

The reason for looping backwards is becasue if you are going from E2 to E100 then when you delete, say, E10 the next cell is going to be E11, when it is actually E10 again, so it'll skip a row. If that makes sense?

 

[01's]

Hey thanks all for your quick replies.... It works......
Cheers