Delete all rows in column that contain 80 or 800

[robw824]

Hello:
I am trying to make code to check each cell in Column K that contains data and if that cell contains 80 or 800 then I want to delete that row.
Wasn't working at all so started by trying to just check for cells containing 80 only and deleting that row
Not doing something right. It will delete 1 or 2 rows then stops deleting.
Here is the code I have now

Public Sub Delete()

Dim Cell As Range

Range("k2").Select
For Each Cell In Sheets("Ref").Range("K:K")
If (Cell = 80) Then
Rows(ActiveCell.Row).EntireRow.Delete
End If
Next Cell
End Sub

Would appreciate any ideas
Thanks
Rob

 

[DanteAmor]

Try this:

With out loop:

Sub Macro_delete()
    Range("K1", Range("K" & Rows.Count).End(3)).AutoFilter 1, "=80", xlOr, "=800"
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    ActiveSheet.ShowAllData
End Sub

Or with For loop:

Sub Macro_delete_2()
  Dim f As Range, rng As Range, c As Range
  
  Set f = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  Set rng = Range("K" & f.Row + 1)
  For Each c In Range("K1", Range("K" & Rows.Count).End(3))
    If c.Value = 80 Or c.Value = 800 Then
      Set rng = Union(rng, c)
    End If
  Next
  rng.EntireRow.Delete
End Sub

 

[Burrgogi]

Hello:
I am trying to make code to check each cell in Column K that contains data and if that cell contains 80 or 800 then I want to delete that row.
Wasn't working at all so started by trying to just check for cells containing 80 only and deleting that row
Not doing something right. It will delete 1 or 2 rows then stops deleting.
Here is the code I have now

Public Sub Delete()

Dim Cell As Range

Range("k2").Select
For Each Cell In Sheets("Ref").Range("K:K")
If (Cell = 80) Then
Rows(ActiveCell.Row).EntireRow.Delete
End If
Next Cell
End Sub

Would appreciate any ideas
Thanks
Rob

I found a thread discussion that closely matches your request.

Here is the modified script:

Sub Delete_Rows()
Dim lastRow As Long, n As Long
Dim tValue1 As Integer
Dim tValue2 As Integer
tValue1 = 80
tValue2 = 800
'lastRow = ThisWorkbook.Sheets("Ref").Range("K2" & Rows.Count).End(xlUp).Row
lastRow = Sheets("Ref").Range("K" & Rows.Count).End(xlUp).Row
    For n = lastRow To 1 Step -1
    If Worksheets("Ref").Cells(n, "K").Value = tValue1 Or Worksheets("Ref").Cells(n, "K").Value = tValue2 Then Worksheets("Ref").Rows(n).EntireRow.Delete
    Next
End Sub

I'm assuming you have column headers in your sheet. If you do not, then change K2 to K1.

Also next time you should wrap your code using VBA tags so that it's easier to read. There's a VBA code button at the top of your screen when you post here.

 

[MikeVol]

It is possible in one line, using the same logic that I used @DanteAmor when using AutoFilter.

    Range("K2", Cells(Rows.Count, "K").End(xlUp)).AutoFilter Field:=1, Criteria1:="=80", Operator:=xlOr, Criteria2:="=800": On Error Resume Next: Range("K2", Cells(Rows.Count, "K").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete: On Error GoTo 0: ActiveSheet.AutoFilterMode = False

 

[robw824]

Try this:

With out loop:

Sub Macro_delete()
    Range("K1", Range("K" & Rows.Count).End(3)).AutoFilter 1, "=80", xlOr, "=800"
    ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
    ActiveSheet.ShowAllData
End Sub

Or with For loop:

Sub Macro_delete_2()
  Dim f As Range, rng As Range, c As Range
 
  Set f = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  Set rng = Range("K" & f.Row + 1)
  For Each c In Range("K1", Range("K" & Rows.Count).End(3))
    If c.Value = 80 Or c.Value = 800 Then
      Set rng = Union(rng, c)
    End If
  Next
  rng.EntireRow.Delete
End Sub

Works fine. Thanks for the help