Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Rows Based on Words Cue Excel Discussion (Misc queries) 2 June 4th 08 06:42 AM
Macro to delete rows based on a condition Darrilyn Excel Worksheet Functions 1 September 6th 07 12:12 AM
How can I delete rows programmatically based on certain criteria? nt_artagnian[_2_] Excel Worksheet Functions 1 March 7th 07 06:48 PM
Delete rows based on criteria Chris_t_2k5 Excel Discussion (Misc queries) 2 April 11th 06 01:52 PM
Delete rows based on certain criteria Coal Miner Excel Discussion (Misc queries) 2 March 3rd 06 06:56 PM


All times are GMT +1. The time now is 09:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"