Delete range of rows based on cell values??

[iwan0285]

Hi,

I've looked through the forum but can't find this precis issue.

I have a spreadsheet which I look for the values in a column (column C), and if the cell value = zero, the row is deleted. this works from the bottom of the sheet and looks at all rows up to row 11. The code for this is below.

Sub DelRow()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 11 Step -1
    If Range("C" & i).Value = 0 Then Rows(i).delete
Next i
End Sub

This works fine, but what I want now is to do something similar, but only looking at a specific range of rows, namely rows 11-20 in column 'E' - deleting the row if the cell value in column E = 0. I can't seem to get it to work - any help would be greatly appreciated! Thanks

 

[Colin Legg]

There are better ways to do this but let's use your existing code as a basis.

Sub DelRow()

  Dim LR As Long, i As Long
 
  LR = Range("C" & Rows.Count).End(xlUp).Row
 
  For i = LR To 11 Step -1
  If Range("C" & i).Value = 0 Then Rows(i).Delete
  Next i
 
End Sub

You want a static range - rows 11 to 20 - so instead of dynamically finding the last row you can just set it to 20:

Sub DelRow()

  Dim LR As Long, i As Long
 
  LR = 20
 
  For i = LR To 11 Step -1
  If Range("C" & i).Value = 0 Then Rows(i).Delete
  Next i
 
End Sub

Next you want to check the values in column E rather than column C, so we can change the column reference. Also, the LR variable looks a bit redundant now so it should either be changed to a constant or just removed, whichever you prefer:

Sub DelRow()

  Dim i As Long
 
  For i = 20 To 11 Step -1
  If Range("E" & i).Value = 0 Then Rows(i).Delete
  Next i
 
End Sub

Does that make sense?

Note that the comparison of a cell's value to 0 like the way you're doing will also result in empty cells being deleted.