CODEWITHSUNDEEP
vbaexcel
user112947
user112947

Reputation: 473

Deleting rows with cells of certain values

Sub workbook()
  Dim LR As Long, i As Long
  LR = Range("E:F" & rows.Count).End(xlUp).Row
  For i = LR To 1 Step -1
  If (Range("E:F" & i).value < 400) Then rows(i).Delete
  Next i
End Sub

I am trying to delete rows in columns E and F on my excel worksheet that have values less than 400.

However, the Range function does not seem to work when selecting two columns at a time (without a specific range such as E1:F10).

What am I doing wrong?

Upvotes: 2

Views: 238

Answers (2)

Louis. B
Louis. B

Reputation: 287

If you want to delete rows that have value greater than 400 either in column "E" or "F" you should try something like :

Sub workbook()

  Dim LR As Long, i As Long
  LR = Range("E" & rows.Count).End(xlUp).Row
  For i = LR To 1 Step -1
      If Range("E" & i).value < 400 or Range("F" & i).value < 400 Then 
          rows(i).Delete
      End If
  Next i

End Sub

If you require both columns to have values greater than 400, replace or by and.

Upvotes: 3

findwindow
findwindow

Reputation: 3153

Sub workbook()

Dim LR As Long, i As Long
LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row

For i = LR To 1 Step -1
    If Range("E" & i).Value < 400 And Range("F" & i).Value < 400 Then Rows(i).Delete
Next i

End Sub

Upvotes: 3

Related Questions