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 RangeDim i As Long, j As Long, n As Long, t As LongDim s As StringApplication.ScreenUpdating = FalseApplication.StatusBar = "Deleting Unwanted Rows"With Sheets("Table") Set rg = .Range("A2").CurrentRegionEnd Withn = rg.Rows.CountFor 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 IfNextWith Sheets("Table") Set rg = .Range("C2").CurrentRegionEnd Withn = rg.Rows.CountFor i = n To 1 Step -1 t = IsNumeric(Cells(i, 1)) If (t = "4") Then rg.Rows(i).EntireRow.Delete j = j + 1 End IfNextApplication.StatusBar = FalseEnd Sub
Sub e_DeleteUnwanted()Dim rg As RangeDim i As Long, j As Long, n As Long, t As LongDim s As StringApplication.ScreenUpdating = FalseApplication.StatusBar = "Deleting Unwanted Rows"With Sheets("Table") Set rg = .Range("A2").CurrentRegionEnd Withn = rg.Rows.CountFor 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 IfNextApplication.StatusBar = FalseEnd Sub
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.
pls try
Open in new window
Regards