Macro Excel - Delete Unwanted Rows

mldaigle1
mldaigle1 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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

Author

Commented:
actually,

the MGMT string is in columnA
and the number to check is in columnC
Top Expert 2016

Commented:
see the diff between

rg.Cells(i, 3) Like "4*" and
rg.Cells(i, 1).Value
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

       If (s = "MGMT") Or rg.Cells(i, 3) Like "4*" Then
Top Expert 2016
Commented:
it worked perfectly on your example
EE20160210_1.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

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

Open in new window

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

Author

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 Manager

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial