Delete entire row if cell contains the string X

Question

I am trying to come up with a way to delete all rows (and shift cells up, if possible) where the website column cell contains the word none. The table contains 5000+ records and this would save me a great amount of time.

I appreciate any suggestions.

There is an answer that is almost exactly what you're looking for. You'll just need to modify the criteria for the autofilter. [Efficient way to delete entire row if...](http://stackoverflow.com/a/16901714/138938) — Jon Crowell, Jul 12 '13 at 04:16

MBK · Accepted Answer · 2020-06-02T10:44:08.177

70

This is not necessarily a VBA task - This specific task is easiest sollowed with Auto filter.

1.Insert Auto filter (In Excel 2010 click on home-> (Editing) Sort & Filter -> Filter)
2. Filter on the 'Websites' column
3. Mark the 'none' and delete them
4. Clear filter

edited Jun 02 '20 at 10:44

answered Jul 12 '13 at 10:55

MBK

899
6
7

3

Except the OP asks specifically how to do it via VBA. – FreeSoftwareServers Nov 14 '19 at 04:26

score 19 · Answer 2 · edited Oct 02 '14 at 12:20

19

Ok I know this for VBA but if you need to do this for a once off bulk delete you can use the following Excel functionality: http://blog.contextures.com/archives/2010/06/21/fast-way-to-find-and-delete-excel-rows/ Hope this helps anyone

Example looking for the string "paper":

In the Find and Replace dialog box, type "paper" in the Find What box.
Click Find All, to see a list of cells with "paper"
Select an item in the list, and press Ctrl+A, to select the entire list, and to select all the "paper" cells on the worksheet.
On the Ribbon's Home tab, click Delete, and then click Delete Sheet Rows.

edited Oct 02 '14 at 12:20

Matt

45,022
8
78
119

answered Oct 02 '14 at 11:33

Barry Carter

191
1
3

1

Brilliant! The only way I could find to do this in bulk (not using VBA). – Holf Jun 07 '16 at 19:30
1

This is the answer that should have been accepted. Using filters is unfeasible when you have 50,000+ rows – Evil Washing Machine Jan 10 '17 at 15:58
FYI: This is a windows only solution, does not work in OSX Excel unfortunatley. – Alan Ennis Jan 30 '18 at 10:43

score 8 · Answer 3 · answered Jun 01 '15 at 09:29

In the "Developer Tab" go to "Visual Basic" and create a Module. Copy paste the following. Remember changing the code, depending on what you want. Then run the module.

  Sub sbDelete_Rows_IF_Cell_Contains_String_Text_Value()
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 390
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 5).Value = "none" Then
            Rows(iCntr).Delete
        End If
    Next
    End Sub

lRow : Put the number of the rows that the current file has.

The number "5" in the "If" is for the fifth (E) column

score 7 · Answer 4 · answered Sep 08 '15 at 16:13

7

I'd like to add to @MBK's answer. Although I found @MBK's answer to be very helpful in solving a similar problem, it'd be better if @MBK included a screenshot of how to filter a particular column.

answered Sep 08 '15 at 16:13

palmbardier

103
1
7

Soulfire · Answer 5 · 2013-07-12T02:50:15.177

This was alluded to in another comment, but you could try something like this.

Sub FilterAndDelete()

Application.DisplayAlerts = False 

     With Sheet1 'Change this to your sheet name

         .AutoFilterMode = False   
         .Range("A3:K3").AutoFilter
         .Range("A3:K3").AutoFilter Field:=5, Criteria1:="none"
         .UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete 

     End With

Application.DisplayAlerts = True

End Sub

I haven't tested this and it is from memory, so it may require some tweaking but it should get the job done without looping through thousands of rows. You'll need to remove the 11-Jul so that UsedRange is correct or change the offset to 2 rows instead of 1 in the .Offset(1,0).

Generally, before I do .Delete I will run the macro with .Select instead of the Delete that way I can be sure the correct range will be deleted, that may be worth doing to check to ensure the appropriate range is being deleted.

matzone · Answer 6 · 2013-07-12T07:21:59.127

0

Try this ...

Dim r as Range
Dim x as Integer

For x = 5000 to 4 step -1 '---> or change as you want //Thanx 4 KazJaw

  set r = range("E" & format(x))
  if ucase(r.Value) = "NONE" then
    Rows(x).EntireRow.Delete
  end if 

Next

edited Jul 12 '13 at 07:21

answered Jul 12 '13 at 02:14

matzone

5,703
3
17
20

With 5000+ records its better to filter and delete rather than looping. – Santosh Jul 12 '13 at 02:22
1

@matzone, you shouldn't use that kind of loop for deleting process. Always use `For i=5000 to 4 Step -1` if you want to delete all elements of certain type for sure! – Kazimierz Jawor Jul 12 '13 at 06:30

score -1 · Answer 7 · edited Mar 26 '20 at 20:06

Delete rows 1 and 2 so that your headings are on row 1
Put this in a macro (IT WILL CHECK THROUGH ROW 75000, YOU CAN LOWER THE NUMBER IF YOU WOULD LIKE

Columns("E:E").Select Selection.AutoFilter ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1, Criteria1:="none" Range("E2:E75000").SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete ActiveSheet.Cells.EntireRow.Hidden = False ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1 Columns("E:E").Select Selection.AutoFilter Range("E2").Select Range("A1").Select

Delete entire row if cell contains the string X

7 Answers7

Linked

Related