Deleting Rows By Value In a Column - VBA Not Working 100% - HELP IS APPRECIATED

K

kescco

Board Regular
Joined
Sep 14, 2009
Messages
174
Okay,

I have about 50 rows data and I would like to delete all rows containing the word "Apple" in Column J.

This code gets most values but not all.

Please help.


Sub DeleteAPPLERows() 'or Button1_Click()
 Dim Rng As Range, Cell As Range
Set Rng = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
For Each Cell In Rng
If Cell = "Apple" Then
Cell.EntireRow.Delete
End If
Next Cell
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
In the rows that are not deleted, is it because it is a partial match? Extra spaces after the word. Also This <> this

perhaps you might be better off using a find delete method
 
Upvote 0
You must delete rows starting from the bottom and For Each starts from the top. When you start from the top, adjacent row that meet your criteria don't all get deleted because once the first one is deleted, the second one moves up to take the place of the deleted one, then the loop iterates to the next row, but that second one is no longer there (because it moved up). Try it this way...
Code: 
Sub DeleteAPPLERows()
  Dim R As Long, LastRow As Long
  LastRow = Range("J" & Rows.Count).End(xlUp).Row
  For R = LastRow To 2 Step -1
    If Cells(R, "J").Value = "Apple" Then
      Cell.EntireRow.Delete
    End If
  Next Cell
End Sub
 
Upvote 0
Rick Rothstein said:
You must delete rows starting from the bottom and For Each starts from the top. When you start from the top, adjacent row that meet your criteria don't all get deleted because once the first one is deleted, the second one moves up to take the place of the deleted one, then the loop iterates to the next row, but that second one is no longer there (because it moved up). Try it this way...
Code: 
Sub DeleteAPPLERows()
  Dim R As Long, LastRow As Long
  LastRow = Range("J" & Rows.Count).End(xlUp).Row
  For R = LastRow To 2 Step -1
    If Cells(R, "J").Value = "Apple" Then
      Cell.EntireRow.Delete
    End If
  Next Cell
End Sub
Click to expand...
Note: If the data in Column J are constants (that is, not formulas), then you can use this shorter, faster macro instead...
Code: 
Sub DeleteAPPLERows()
  Columns("J").Replace "Apple", "#N/A", xlWhole, , False
  Columns("J").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
 
Upvote 0
Here is more error proof version of detecting the last row. Deletes row if cell contains word "apple" (not case sensitive).
Code: 
Sub DeleteAPPLERows() 'or Button1_Click()
Dim thelastrow As Long, i As Long
Dim sheetname As String
Dim v As Variant
sheetname = "Sheet1" 'name your sheet
 
thelastrow = ThisWorkbook.Sheets(sheetname).Cells.Find(What:="*", After:=[A1], LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
v = ThisWorkbook.Sheets(sheetname).Range("j2:j" & thelastrow) 'column J, row from 2nd row to the last non empty row
 
 For i = UBound(v) To LBound(v) Step -1 'loop backwards to avoid wrong identification of row number after one has been deleted
    If InStr(1, LCase(v(i, 1)), "apple") <> 0 Then
        ThisWorkbook.Sheets(sheetname).Rows(i + 1).EntireRow.Delete
    End If
 Next i
 
 End Sub
 
Last edited:
Upvote 0
Rick Rothstein said:
Note: If the data in Column J are constants (that is, not formulas), then you can use this shorter, faster macro instead...
Code: 
Sub DeleteAPPLERows()[COLOR=#FF0000][/COLOR]
  [COLOR=#FF0000][B]On Error Resume Next[/B][/COLOR]
  Columns("J").Replace "Apple", "#N/A", xlWhole, , False
  Columns("J").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  [COLOR=#FF0000][B]On Error GoTo 0[/B][/COLOR]
End Sub
Click to expand...
Taisho's mentioning of "error proof" reminded me that the above red code lines should be included with the shorter, fastr macro to protect against none of the cells in Column J being equal to the word "apple".
 
Upvote 0
GooberTron said:
Is dumping the range into an array particularly faster than looping through the row number?
Click to expand...

Yes, the more so as the number of rows increases, but most of the time will be spent deleting the rows if there are more than a few, in which case it will not make much difference.
 
Upvote 0
JoeMo said:
Yes, the more so as the number of rows increases, but most of the time will be spent deleting the rows if there are more than a few, in which case it will not make much difference.
Click to expand...
Which is where I think the code I posted in Message #4 and modified slightly in Message #6 comes into play... as long as Column J contains constants, I believe my code will be faster than any looping solution (using a Filter and deleting visible rows would probably be faster still).
 
Upvote 0
Rick Rothstein said:
Which is where I think the code I posted in Message #4 and modified slightly in Message #6 comes into play... as long as Column J contains constants, I believe my code will be faster than any looping solution (using a Filter and deleting visible rows would probably be faster still).
Click to expand...
No question about it in my mind - I have pirated and used your clever approach shamelessly more than a few times and where there are more than a handful of rows to delete it seems markedly faster. The time saved is time not spent "acquiring" the rows that must be deleted. Specialcells does that very quickly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnPoole
  • Solved
Delete rows based on unique values
2
Replies
17
Views
867
kevin9999
K
F
  • Solved
VBA code modification sumif
Replies
2
Views
294
FGaxha
F
E
  • Solved
Trying to Delete row
Replies
4
Views
251
Georgiboy
Georgiboy
Nelson78
  • Question
VBA: how to delete filtered rows (multiple criteria)
Replies
5
Views
270
Fluff
Fluff
I
  • Solved
vba Code shows wrong format in cell & autosum also ignors the value
Replies
3
Views
258
RobP
R

Forum statistics

Threads
1,214,605
Messages
6,120,473
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top