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

User · Answer

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

User · Answer

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

User · Answer

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  amp  Filter -  Filter   2  Filter on the  Websites  column  3  Mark the  none  and delete them  4  Clear filter

User · Answer

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

User · Answer

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

User · Answer

Try this      Dim r as Range Dim x as Integer  For x   5000 to 4 step -1  --- gt  or change as you want   Thanx 4 KazJaw    set r   range  E   amp  format x     if ucase r Value     NONE  then     Rows x  EntireRow Delete   end if   Next

User · Answer

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

[excel] Delete entire row if cell contains the string X

Examples related to excel

Examples related to vba

Examples related to excel-2010

Examples related to excel-2007