Delete Rows based on value

[Sabosis]

Hello-

I have tried multiple examples to get rows deleted where the value in
column B is less than 50. In my code, before the attached code runs, I
have formatted column B as a number, no decimals. Can anyone tell me
why the code is erroring our?

Sub DeleteCallTags()
'will delete rows where column B <50
Dim cell As Range
Dim delRange As Range
For Each cell In Range("B1:B" & Range("B" &
Rows.Count).End(xlUp).Row)
If cell.Value < 50 Then ******This is the line that
gets highlighted when I hit "debug"
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange, cell)
End If
End If
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

 

[Sheeloo]

'Declare lastRow as
Dim as lastRow as Long

'Then use it like this
lastRow = Cells(Rows.Count, "B").End(xlUp).Row

'Then the loop as
For Each cell In Range("B1:B" & lastRow)

 

[Mike H]

Hi,

There's a fundamental problem with the code because working forward through
a range to delete rows and deleting as you go causes rows to be missed if 2
adjacent rows meet the criteria. However, I can see nothing wrong with the
line you highlighted. Try this instead

Sub DeleteCallTags()
'will delete rows where column B <50
Dim cell As Range
Dim delRange As Range
lastrow = Range("B" & Rows.Count).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 2).Value < 50 Then ' ******This is the line that
gets
If delRange Is Nothing Then
Set delRange = Cells(x, 2)
Else
Set delRange = Union(delRange, Cells(x, 2))
End If
End If
Next
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

Mike

 

[Bernie Deitrick]

Deleting row by row, even if you create a range of separated rows, can be quite slow. Better to
sort the range first based on your deletion criteria.

Try your macro this way:

Sub DeleteLessThan50()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").Value = "Status"
myRows = ActiveSheet.UsedRange.Rows.Count
With Range("A2:A" & myRows)
.FormulaR1C1 = "=IF(RC[2]< 50,""Trash"",""Keep"")"
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlYes
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Range("A" & myRows)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Sabosis]

Deleting row by row, even if you create a range of separated rows, can bequite slow.  Better to
sort the range first based on your deletion criteria.

Try your macro this way:

Sub DeleteLessThan50()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").Value = "Status"
myRows = ActiveSheet.UsedRange.Rows.Count
With Range("A2:A" & myRows)
   .FormulaR1C1 = "=IF(RC[2]< 50,""Trash"",""Keep"")"
   .Copy
   .PasteSpecial Paste:=xlValues
End With
Cells.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlYes
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Range("A" & myRows)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

Hello-

I have tried multiple examples to get rows deleted where the value in
column B is less than 50. In my code, before the attached code runs, I
have formatted column B as a number, no decimals. Can anyone tell me
why the code is erroring our?

Sub DeleteCallTags()
'will delete rows where column B <50
Dim cell As Range
       Dim delRange As Range
       For Each cell In Range("B1:B" & Range("B" &
Rows.Count).End(xlUp).Row)
           If cell.Value < 50 Then    ******This is theline that
gets highlighted when I hit "debug"
               If delRange Is Nothing Then
                   Set delRange = cell
               Else
                   Set delRange = Union(delRange,cell)
               End If
           End If
       Next cell
       If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub- Hide quoted text -

- Show quoted text -

Thanks Bernie, it worked like a charm! I really appreciate the help!

Scott