Macro to delete rows based on partial text

**rossi_69** · 04-09-2009, 05:00 PM

Hi all,

I am looking for a macro to delete entire rows based on the a partial text contained in column A.

For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.

BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4

the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.

I am a novice at VBA and any help would be apreciated Thanks.

**rylo** · 04-09-2009, 05:11 PM

Hi

You may want to change the finishing row but try

Please Login or Register  to view this content.

rylo

**rossi_69** · 04-09-2009, 05:31 PM

Rylo thanks for the reply,

I ran the code as below and could not get any results,

PHP Code:


Sub Delete_rows() 
 
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 
   
    If Left(Cells(i, 1), 7) = "Booklet" Or Left(Cells(i, 1), 5) = "Lotto" Or Left(Cells(i, 1), 3) = "PBT" Then 
      Cells(i, 1).EntireRow.Delete 
    End If 
   
  Next i 
 
End Sub

any ideas, I attached an example of some of the spreadsheet to help.

Thanks again.
Rossi

**foxguy** · 04-09-2009, 05:39 PM

Your macro has "Booket", but Column A contains "BOOKLET"
change the line to:

Please Login or Register  to view this content.

**rylo** · 04-09-2009, 06:18 PM

rossi

Yep foxguy is right about the case sensitivity. I just grabbed your comment

need to select based on Booklet, Lotto and PBT

and based the selection on that.

rylo

**rossi_69** · 04-09-2009, 06:25 PM

Thanks guys,

I discovered that this macro takes ages so i modified it with the following code to make it quicker and turned off redrawing.

PHP Code:


Sub Delete_rows_CTD_SLOTS() 
 
With Application 
 
        .Calculation = xlCalculationManual 
 
        .ScreenUpdating = False 
 
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 
   
    If Left(Cells(i, 1), 7) = "BOOKLET" Or Left(Cells(i, 1), 10) = "TOURNAMENT" Or Left(Cells(i, 1), 5) = "LOTTO" Or Left(Cells(i, 1), 3) = "PBT" Or Left(Cells(i, 1), 4) = "SEAT" Or Left(Cells(i, 1), 5) = "PRIZE" Or Left(Cells(i, 1), 4) = "BOOK" Or Left(Cells(i, 1), 6) = "PLAYER" Then 
      Cells(i, 1).EntireRow.Delete 
    End If 
   
  Next i 
   
        .Calculation = xlCalculationAutomatic 
 
        .ScreenUpdating = True 
 
    End With 
 
End Sub

Works like a charm now.