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
mldaigle1Asked:
Who is Participating?
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.

Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

Regards
mldaigle1Author Commented:
actually,

the MGMT string is in columnA
and the number to check is in columnC
Rgonzo1971Commented:
see the diff between

rg.Cells(i, 3) Like "4*" and
rg.Cells(i, 1).Value
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

       If (s = "MGMT") Or rg.Cells(i, 3) Like "4*" Then
Rgonzo1971Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

prabhu rajendranCommented:
Guess it should be.
 If (s = "MGMT") Or rg.Cells(i, 3).Text Like "4*" Then

Open in new window

Roy CoxGroup Finance ManagerCommented:
You could add a helper column which could be hidden Then run some code to remove the unwanted rows. See attached file
test.xlsm
mldaigle1Author Commented:
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

:)
Roy CoxGroup Finance ManagerCommented:
Avoiding Loops for tasks like this will make your code more efficient.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.