Macro to delete rows where value < 1 or where value is zero or >= -1

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,669
Office Version
  1. 2019
Platform
  1. Windows
I have values in Col W on sheet imported data

I wanted to delete these rows where the values are < 1 or where value is zero or >= -1


Where running the macro all the rows from row 2 is being deleted

Kindly check my code and kindly amend



Code:
 Sub Delete_itemslessthan1_IncludingZeroes()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim LR As Long, i As Long
With Sheets("Imported Data")
LR = .Cells(Rows.Count, "A").End(xlUp).Row

For i = LR To 2 Step -1
If (((.Cells(i, 23).Value) <= 1) Or ((.Cells(i, 23).Value) <= 1) Or _
((.Cells(i, 23).Value) = 0)) Or ((.Cells(i, 23).Value) >= -1) Then .Rows(i).EntireRow.Delete
Next i
End With

 Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,409
Office Version
  1. 2013
Platform
  1. Windows
How about
VBA Code:
Sub Delete_itemslessthan1_IncludingZeroes()
Application.ScreenUpdating = False
Dim LR As Long, i As Long
With Sheets("Imported Data")
LR = .Cells(Rows.Count, "AW").End(xlUp).Row
    For i = LR To 2 Step -1
        If .Cells(i, 23).Value <= 1 And .Cells(i, 23).Value >= -1 Then .Rows(i).Delete
    Next i
End With
 Application.ScreenUpdating = True
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,669
Office Version
  1. 2019
Platform
  1. Windows
Thanks Michael. Code works perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,114,530
Messages
5,548,583
Members
410,854
Latest member
ajbaluyut
Top