Macro Excel - Delete Unwanted Rows

Hello Experts,

I'm trying to adjust a macro that delete rows when meet on of the 2 following criteria:

- when cell content start with "MGMT" in column A (from A2 up to the end of the table)
or
- when cell content start with the number "4" in column C (from C2 up to the end of the table)

Can you help? I'm having problem with the second criteria (the cell content that start with the number "4")

Sample attached.

Sub e_DeleteUnwanted()

Dim rg As Range
Dim i As Long, j As Long, n As Long, t As Long
Dim s As String


Application.ScreenUpdating = False
Application.StatusBar = "Deleting Unwanted Rows"

With Sheets("Table")
   Set rg = .Range("A2").CurrentRegion
End With
n = rg.Rows.Count

For i = n To 1 Step -1
       s = UCase(Left(rg.Cells(i, 1).Value, 4))
       If (s = "MGMT") Then 
         rg.Rows(i).EntireRow.Delete
         j = j + 1
       End If
Next

With Sheets("Table")
   Set rg = .Range("C2").CurrentRegion
End With
n = rg.Rows.Count

For i = n To 1 Step -1
       t = IsNumeric(Cells(i, 1))
       If (t = "4") Then
         rg.Rows(i).EntireRow.Delete
         j = j + 1
       End If
Next

Application.StatusBar = False
End Sub

Open in new window

test.xlsx

Sub e_DeleteUnwanted() Dim rg As Range Dim i As Long, j As Long, n As Long, t As Long Dim s As String Application.ScreenUpdating = False Application.StatusBar = "Deleting Unwanted Rows" With Sheets("Table") Set rg = .Range("A2").CurrentRegion End With n = rg.Rows.Count For i = n To 1 Step -1 s = UCase(Left(rg.Cells(i, 1).Value, 4)) If (s = "MGMT") Or rg.Cells(i, 3) Like "4*" Then rg.Rows(i).EntireRow.Delete j = j + 1 End If Next Application.StatusBar = False End Sub

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 55

Rgonzo1971Commented: 2016-02-10

Hi,

pls try

Regards

mldaigle1Author Commented: 2016-02-10

actually,

the MGMT string is in columnA
and the number to check is in columnC

see the diff between

rg.Cells(i, 3) Like "4*" and
rg.Cells(i, 1).Value

just tried and it return me a type mismatch error on line

If (s = "MGMT") Or rg.Cells(i, 3) Like "4*" Then

it worked perfectly on your example
EE20160210_1.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 45

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2016-02-10

Replace your second For loop with this...

For i = n To 1 Step -1
   If Left(Cells(i, 3), 1) = "4" Then
     Rows(i).Delete
   End If
Next

LVL 2

prabhu rajendranCommented: 2016-02-10

Guess it should be.

 If (s = "MGMT") Or rg.Cells(i, 3).Text Like "4*" Then

LVL 29

Roy CoxGroup Finance ManagerCommented: 2016-02-10

You could add a helper column which could be hidden Then run some code to remove the unwanted rows. See attached file
test.xlsm

Thanks everyone,

A mix of all of your solution solve my issue.

Rgonzo1971 + prabhu rajendran = short and sweet, works fine
sktneer = work good if I add .TEXT
Roy_Cox = this is something I never thought, but I've check your XLSM and I will keep it as reference.

Thank you all for the quick and efficient answer.

Kiss you all

/mldaigle

:)

Avoiding Loops for tasks like this will make your code more efficient.

Macro Excel - Delete Unwanted Rows

Who is Participating?