Delete multiple rows having specific texts

M

mshahbaz

New Member
Joined
Feb 13, 2017
Messages
36
Hi,

I want to delete all rows having anyone of below texts.

1. Printed
2. Capsule
3. Tablet

Please advise quick techniques.

Shahbaz

For example:

Column 2

Printed aluminium sheet
Sheets printed
Acetaminophen tablets
Vit. E capsules
Capsules of vitamins
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
=IFERROR(FIND("PRINTED",UPPER(B1),1),0)+IFERROR(FIND("CAPSULE",UPPER(B1),1),0)+IFERROR(FIND("TABLET",UPPER(B1),1),0)

Enter this formula in column 3. filter zeros and delete them.
 
Upvote 0
Hi

I entered this formula in "column C" and all values became Zero. While i just want to remove those rows which
contain specific words in their text description.

Shahbaz
 
Upvote 0
What column should we look in for the values?

In post #1 you said: Column 2
In Post #3 you said:
I entered this formula in "column C"

Are you willing to use Vba ?
And you want to delete the entire row correct ?
 
Last edited:
Upvote 0
Assuming your looking for the values in column (2)
And assuming you want to delete the entire row

Try this:

Code: 
Sub Delete_If()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If InStr(Cells(i, 2).Value, "Tablet") Or _
            InStr(Cells(i, 2).Value, "Capsule") Or _
            InStr(Cells(i, 2).Value, "Printed") Then
            Rows(i).Delete
        End If
    Next
Application.ScreenUpdating = True
'Tablet
'Capsule
'Printed
End Sub
 
Upvote 0
mshahbaz said:
Please advise quick techniques.
Click to expand...
Assuming the above is important and the following example means that you would want all of those rows deleted ..

mshahbaz said:
For example:

Column 2

Printed aluminium sheet
Sheets printed
Acetaminophen tablets
Vit. E capsules
Capsules of vitamins
Click to expand...
.. then give this a try in a copy of your workbook.
Rich (BB code): 
Sub Del_Rows()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
  Dim s As String
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    s = LCase(a(i, 1))
    Select Case True
      Case s Like "*printed*", s Like "*capsule*", s Like "*tablet*"
        k = k + 1
        b(i, 1) = 1
    End Select
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

@bhos123
Two points in relation to your suggested formula/method
1. If you use SEARCH() instead of FIND() you don't need to use UPPER() as well since SEARCH is not case-sensitive.
2. Wouldn't you need to filter for non-zeros to delete the rows containing those texts?
 
Upvote 0
Peter: And why did you think my script would not work? I tested it.
Peter_SSs said:
Assuming the above is important and the following example means that you would want all of those rows deleted ..

.. then give this a try in a copy of your workbook.
Rich (BB code): 
Sub Del_Rows()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
  Dim s As String
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    s = LCase(a(i, 1))
    Select Case True
      Case s Like "*printed*", s Like "*capsule*", s Like "*tablet*"
        k = k + 1
        b(i, 1) = 1
    End Select
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

@bhos123
Two points in relation to your suggested formula/method
1. If you use SEARCH() instead of FIND() you don't need to use UPPER() as well since SEARCH is not case-sensitive.
2. Wouldn't you need to filter for non-zeros to delete the rows containing those texts?
Click to expand...
 
Upvote 0
MAIT: Why did you assume I thought your code wouldn't work?
My Aswer Is This said:
Peter: And why did you think my script would not work? I tested it.
Click to expand...

1. Our codes do different jobs. I tested yours and mine. Did you compare? I don't know which of the results the OP wants, do you?

2. The OP asked for "quick techniques". There are two words there.
a) "quick": I pointed out with the initial quote in, & first 5 words of, my post that I was addressing that issue. I don't know how many rows of data the OP has, do you? IF there is say 200,000, my code will be much faster than yours, do you disagree?
b) "techniques": That is plural. Perhaps the OP is looking for a variety of ways to consider which one(s) they like the best, or suit their circumstances the best, or perhaps it's a typo. I don't know which, do you?

3. Do you think the first working solution for any OP should be the only one offered to them? I think not, given the following response to a suggestion after a perfectly good working solution had already been posted in this thread.
My Aswer Is This said:
I'm always looking for the best way to do things.
Click to expand...



bhos123 said:
@Peter. I agree with you. Thanks for the suggestions.
Click to expand...
You are welcome. I'm glad you took my comments as constructive. :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
VBA Delete rows if cell contains specific text within a string
2
Replies
10
Views
924
Peter_SSs
Peter_SSs
E
  • Question
Deleting Rows with Specific Word along the Same Column VBA
Replies
4
Views
281
excelnoob1991
E
P
  • Question
Delete Rows down to a String
Replies
7
Views
198
mumps
mumps
N
  • Solved
Use of TextSplit Function to a cell with multiple text line separated by delimiter
Replies
6
Views
538
Peter_SSs
Peter_SSs
S
  • Question
Delete multiple rows in one go
Replies
6
Views
353
sm789
S

Forum statistics

Threads
1,214,653
Messages
6,120,757
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top