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]

.

Thanks Peter

Both problems resolved.

Good news!

 

[mshahbaz]

When i try to enter this code or any code in command button (Active X control) then there is following error.

Compile error: Expected End Sub

 

[Peter_SSs]

When i try to enter this code or any code in command button (Active X control) then there is following error.

Compile error: Expected End Sub

Why not leave the macro exactly as it was in a standard module and then have your ActiveX button click code like this?

Private Sub CommandButton1_Click()
  Del_Rows
End Sub

 

[mshahbaz]

Why not leave the macro exactly as it was in a standard module and then have your ActiveX button click code like this?

Private Sub CommandButton1_Click()
  Del_Rows
End Sub

Great

 

[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
__________________________________________________


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

Hello Peter,


Just like above code (which is for Column C & E), i used a separate code specifically for
Column B only with below changes. There are some words which should be removed from
Column B only.


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

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




When i try to run both codes simultaneously, there is an error.

 

[Peter_SSs]

Not quite sure what you mean about running both codes simultaneously, but if you are only looking in column B then your change for a = ... is correct but for s it should only need to be

s = LCase(a(i,1))

 

[mshahbaz]

Not quite sure what you mean about running both codes simultaneously, but if you are only looking in column B then your change for a = ... is correct but for s it should only need to be

s = LCase(a(i,1))

I mean when i entered these both codes in Active X button, then there is some error.
Sub Del_Rows() becomes highlighted in Yellow.

 

[Peter_SSs]

I mean when i entered these both codes in Active X button, then there is some error.
Sub Del_Rows() becomes highlighted in Yellow.

You cannot have two procedures with the same name. Change one of the sub names (blue text) to something else.

In future, if you are reporting an error, tell us exactly what the error message is and also what line is highlighted (if a line is highlighted).

 

[mshahbaz]

You cannot have two procedures with the same name. Change one of the sub names (blue text) to something else.

In future, if you are reporting an error, tell us exactly what the error message is and also what line is highlighted (if a line is highlighted).

Hi Peter,


All questions solved. Your continued support collected an extensive amount of time
and long effort into just "One Click".


I am really grateful to you.


God bless you.


Shahbaz

 

[Peter_SSs]

No problem. Glad to help.