Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows Based on Words | Excel Discussion (Misc queries) | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
How can I delete rows programmatically based on certain criteria? | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) |