Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete entire row if cell contains the string X

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!

like image 342
AnchovyLegend Avatar asked Jul 12 '13 01:07

AnchovyLegend


3 Answers

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

like image 151
MBK Avatar answered Sep 25 '22 09:09

MBK


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.
like image 39
Barry Carter Avatar answered Sep 26 '22 09:09

Barry Carter


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

like image 23
Datacrawler Avatar answered Sep 25 '22 09:09

Datacrawler