Avatar of mldaigle1
mldaigle1
Flag for Canada asked on

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
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Roy Cox

8/22/2022 - Mon
Rgonzo1971

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
mldaigle1

ASKER
actually,

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

see the diff between

rg.Cells(i, 3) Like "4*" and
rg.Cells(i, 1).Value
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
mldaigle1

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

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
prabhu rajendran

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Roy Cox

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mldaigle1

ASKER
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 Cox

Avoiding Loops for tasks like this will make your code more efficient.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.