Delete Rows based on value
 

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

Delete Rows based on value
 

'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)

"Sabosis" wrote:

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

Delete Rows based on value
 

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

"Sabosis" wrote:

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

Delete Rows based on value
 

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" wrote in message
...
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

Delete Rows based on value
 

On Oct 28, 11:06*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

Scott