Delete multiple rows having specific texts

[mshahbaz]

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

 

[Peter_SSs]

.. here is another macro solution for you to consider that does not use looping thru the rows in column B.

A few comments

1. Not a problem, but just noting that this seems to be addressing the original question rather than the subsequent changes/additions like posts #12 & 14.

2. I don't think that you tested your solution on a set of data that contained any blank cells in column B.

3. Perhaps I'm mistaken but your comment about not looping seems to be implying that it is preferable not to loop for some reason? If so, I'm not sure what that reason is. For one of my tests I used 10,000 rows of data where approximately half had to be deleted and your code took approx. 12 times longer than the post #6 code.

 

[hiker95]

Peter_SSs,

By the time that I answered mshahbaz's original request, mshahbaz had already changed their requirements.

And, by then there had been successful replies that have solved mshahbaz's latest request.

 

[mshahbaz]

Sorry, I've been away for a few days.

This addition is not clear to me.

a) Do you want rows deleted if they have certain texts in other columns and <25 in column G, or

b) Do you want rows deleted if they have certain text in other columns or <25 in column G, or

c) Is this question unrelated to the previous questions, or

d) Something else?

If a) or b), which columns? There have been a few different requirements during the thread.


In fact, why not give some dummy sample data and explain which rows should be deleted and why?

Hi Peter,

Glad to see you again.

I wanted to delete all rows having values less than 20 in Column "G".

I successfully used filter to remove them.

Thanks
Shahbaz

 

[Peter_SSs]

I wanted to delete all rows having values less than 20 in Column "G".

I successfully used filter to remove them.

OK, thanks for letting us know.
If you are happy then so am I, but if you do happen to have a large amount of data, the deletion could probably be done a bit faster using an adaptation of my earlier code.
Post back if you wanted that adaptation and also post your existing 'filter code' if you used a macro to do those deletions.

 

[mshahbaz]

OK, thanks for letting us know.
If you are happy then so am I, but if you do happen to have a large amount of data, the deletion could probably be done a bit faster using an adaptation of my earlier code.
Post back if you wanted that adaptation and also post your existing 'filter code' if you used a macro to do those deletions.

Below is the code you advised me earlier for Column C and Column E.

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("C1", Range("E" & 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) & "|" & a(i, 3))
Select Case True
Case s Like "*tablet*", s Like "*capsule*"
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
__________________________________________________

Below is the dummy data:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
21 Jan	2371.1234	Item 1	Company	Supplier	Origin	12
22 Jan	3905.9990	Item 2	Company	Supplier	Origin	1500
23 Jan	3905.2632	Item 3	Company	Supplier	Origin	650
24 Jan	1702.1110	Item 4	Company	Supplier	Origin	10

<tbody>
</tbody>

I want to delete those rows having value less than 20 in Column G.

 

[Peter_SSs]

Below is the code you advised me earlier for Column C and Column E.

1. Does that mean you did the filter of column G and delete rows (as mentioned in post #23) manually, not by code?

 

[mshahbaz]

1. Does that mean you did the filter of column G and delete rows (as mentioned in post #23) manually, not by code?

Yes, i used filter on columns then delete them manually.

 

[mshahbaz]

Below is the code you advised me earlier for Column C and Column E.

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("C1", Range("E" & 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) & "|" & a(i, 3))
Select Case True
Case s Like "*tablet*", s Like "*capsule*"
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
__________________________________________________

Below is the dummy data:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
21 Jan	2371.1234	Item 1	Company	Supplier	Origin	12
22 Jan	3905.9990	Item 2	Company	Supplier	Origin	1500
23 Jan	3905.2632	Item 3	Company	Supplier	Origin	650
24 Jan	1702.1110	Item 4	Company	Supplier	Origin	10

<tbody>
</tbody>

I want to delete those rows having value less than 20 in Column G.

Problem # 02:

There is a problem in this code:

Case s Like "*tablet*", s Like "*capsule*", ................

When i enter words in this code, the space finishes, and it starts from new row like below.

Case s Like "*tablet*", s Like "*capsule*"
s Like "*door*", s Like "*bottle*"

But upon running macros there is an error "Can't execute in break mode".

 

[Peter_SSs]

Yes, i used filter on columns then delete them manually.

You could have that same code delete the other rows as you go by altering these two lines in the code as shown

a = Range("C1", Range("G" & Rows.Count).End(xlUp)).Value

Case s Like "*tablet*", s Like "*capsule*", a(i, 5) < 20

When i enter words in this code, the space finishes, and it starts from new row like below.

I'm not quite sure what you are saying there, but if you are trying to include extra words in the list to be deleted then it would be like this

Case s Like "*tablet*", s Like "*capsule*", s Like "*door*", s Like "*bottle*"

or like this

Case s Like "*tablet*", s Like "*capsule*", _
            s Like "*door*", s Like "*bottle*"

 

[mshahbaz]

You could have that same code delete the other rows as you go by altering these two lines in the code as shown

a = Range("C1", Range("G" & Rows.Count).End(xlUp)).Value

Case s Like "*tablet*", s Like "*capsule*", a(i, 5) < 20

I'm not quite sure what you are saying there, but if you are trying to include extra words in the list to be deleted then it would be like this

Case s Like "*tablet*", s Like "*capsule*", s Like "*door*", s Like "*bottle*"

or like this

Case s Like "*tablet*", s Like "*capsule*", _
            s Like "*door*", s Like "*bottle*"

.

Thanks Peter

Both problems resolved.