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

 

[mshahbaz]

What if i need to apply this code on "Column C" instead of "Column B"?

I made following changes in your vba code, but it is not working on "Column C".

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



Shahbaz

 

[mshahbaz]

What if i need to apply this code on "Column C" instead of "Column B"?

I made following changes in your vba code, but it is not working on "Column C".

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



Shahbaz

It would be much easier if i can apply this formula on Column B & C simultaneously with one vba code.

 

[Peter_SSs]

Hi Peter,

It worked great!

Thanks a lot.

Shahbaz

You are welcome, thanks for the follow-up.

I made following changes in your vba code, but it is not working on "Column C".

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

That is exactly the correct change to make to work on column C. I don't know why it didn't work for you. I tested and it worked for me.

It would be much easier if i can apply this formula on Column B & C simultaneously with one vba code.

If the bottom row of data in col B is on the same row as the bottom row of data in col C and your requirement is to delete rows that have any of your text strings in either column, then just 2 changes should be required.

That same line as above would change to

a = Range("B1", Range("C" & Rows.Count).End(xlUp)).Value

and the line 3 lines below would become

s = LCase(a(i, 1) & "|" & a(i, 2))

If columns B & C could have data ending on different rows, or if you only want to delete a row if one of your texts appears in both columns, then a few more changes would be needed. Post back with more details if that is required in your circumstances.

 

[mshahbaz]

You are welcome, thanks for the follow-up.



That is exactly the correct change to make to work on column C. I don't know why it didn't work for you. I tested and it worked for me.



If the bottom row of data in col B is on the same row as the bottom row of data in col C and your requirement is to delete rows that have any of your text strings in either column, then just 2 changes should be required.

That same line as above would change to

a = Range("B1", Range("C" & Rows.Count).End(xlUp)).Value

and the line 3 lines below would become

s = LCase(a(i, 1) & "|" & a(i, 2))

If columns B & C could have data ending on different rows, or if you only want to delete a row if one of your texts appears in both columns, then a few more changes would be needed. Post back with more details if that is required in your circumstances.

Great!

Just one more change required: On other sheets i need to enter this code for Column C and Column E.



Regards

 

[Peter_SSs]

On other sheets i need to enter this code for Column C and Column E.

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

and

s = LCase(a(i, 1) & "|" & a(i, 3))

 

[mshahbaz]

Done

 

[Peter_SSs]

Done

 

[mshahbaz]

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

and

s = LCase(a(i, 1) & "|" & a(i, 3))

Hi Peter

One more addition needed.

I have to delete all rows having values <25 from Column G only.

Is there any possibility to make an addition in this code without complication, or
should it be a separate macro for it?

 

[Peter_SSs]

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

One more addition needed.

I have to delete all rows having values <25 from Column G only.

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?

 

[hiker95]

mshahbaz,

If I understand you correctly, then here is another macro solution for you to consider that does not use looping thru the rows in column B.

Sample raw data:

Excel 2007
	B
1	Printed aluminium sheet
2	Sheets printed
3	Acetaminophen tablets
4	Vit. E capsules
5	Capsules of vitamins
6	printed
7	capsule
8	tablet
9	Please advise quick techniques.
10	Shahbaz
11	hiker95
12
Sheet1

And, after the macro:

Excel 2007
	B
1	Please advise quick techniques.
2	Shahbaz
3	hiker95
4
5
6
7
8
9
10
11
12
Sheet1

Sub mshahbaz()
' hiker95, 02/19/2017, ME990911
Dim Addr As String
Addr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""printed"",@)),""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""capsule"",@)),""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""tablet"",@)),""#N/A"",@)", "@", Addr))
On Error GoTo NoDeletes
Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End Sub