Link to home
Create AccountLog in
Avatar of mldaigle1
mldaigle1Flag 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
Avatar of Rgonzo1971
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
Avatar of mldaigle1

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.