Delete entire row if cell contains the string X

Question

I am new to VBA and 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. Many thanks in advance!

Answer 1

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

Answer 2

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":

1. In the Find and Replace dialog box, type "paper" in the Find What box.
2. Click Find All, to see a list of cells with "paper"
3. 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.
4. On the Ribbon's Home tab, click Delete, and then click Delete Sheet Rows.

Answer 3

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