Delete rows based on cell value using Excel VBA

Mr. Rao, I found your codes for deleting rows very helpful. Although I use ACL and IDEA for many of my projects, I find Excel very handy for the final analysis.
I am trying to delete rows based on multiple criteria in column L. The macro deletes the rows but there are some rows (esp the second row) and the last row and some in between that do not get deleted. I added some sample rows with random verbiage to see if they would get deleted. I tried some variation on the criteria:
1) Case statement with cell value in L
2) Wild card character using If Not in conjunction with “Like “xxx’” but still the same problem.
3) Not sure if I can use the last row/last column with this one instead of range.
Any help is greatly appreciated.

Thanks
Kindest regards, Rohini

Code: Sub DeleteMyRows()
Application.ScreenUpdating = False
Dim ws As Worksheet, Rng As Range, Cell As Range
Set ws = ActiveSheet
Set Rng = ws.Range(Range(“L2”), Range(“L” & Rows.Count).End(xlUp))
For Each Cell In Rng
If Not Cell.Value “N. HOUSTON ROSLYN- MFG” Or _
Not Cell.Value “MOORE MFG” Or _
Not Cell.Value “N. HOUSTON – TURNKEY” Then
Cell.EntireRow.Delete
End If
Next Cell
Application.ScreenUpdating = true

Hi,
thanks for your code – it is very helpful for BAs like me who do not know coding :)

I would require additional help from you please – I want to delete all rows in a worksheet if Column L of the sheet has a value called ‘CLOSED CONTRACTS’. I copied the code above and did some changes but it couldn’t do it successfully.

Any help is greatly appreciated.

Thanks

Hi. Your code is very helpful and it works ok. However could you help me make it only check column A for the criteria, becuse it seems like the macro is making too much work for my case.
Thank you very much.

Sorry for all the posts, but I’ve come up with this code thus far but it does not work:

Sub Loop_Cuts()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To Firstrow Step -1

With .Cells(Lrow, “I”)

If Not IsError(.Value) Then
Select Case .Value
Case Is = “ASSET BACKED”, “CASH”, “CMBS”, “COMMINGKED FUND”, “CONVERTIBLES”, “CORPORATE”, “MORTGAGE PASS-THROUGH”, “MUNICIPAL”, “MUTUAL FUNDS”, “CMO”, “AGENCY”: .EntireRow.Delete Or_

With .Cells(Lrow, “G”)

If Not IsError(.Value) Then
Select Case .Value
Case Is = “CREDIT CARD”, “NON-SECY ASSET-STOCK”, “SHORT TERMS”: .EntireRow.Delete Or_

With .Cells(Lrow, “E”)

If Not IsError(.Value) Then
Select Case .Value
Case Is = “UNITED STATES OF AMERICA (THE)”: .EntireRow.Delete Or_

With .Cells(Lrow, “E”)

ElseIf Not IsError(.Value) Then
Select Case .Value
Case Is = “‘”: .EntireRow.Delete

End Select

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Hi,
I need an Excel macro, to match the two excels, if two excels have the same value in a particular column, then the entire row should be deleted from the first Excel sheet.

I want it like, we should enter the column which we need to match(A or B or C or….)

E.g.,
Excel 1

A B C
1 100 200 jack
2 111 300 Kim
3 112 400 katie
4 123 500 Rosie

Excel 2

A B C
1 111 300 Kim
2 123 500 Rosie

Result should be:

A B C
1 100 200 jack
2 112 400 katie

Is there a way to make this run continuously in the back ground?

For example I have a drop down and every time “Delivered” is selected I want the row to deleted.

In a dump, i need to keep only the rows which matches my other database in separate excel, for example, in my dump column A have n number of names but in my main database I have only 70 names. I just want the 70 names and rest of the rows need to be deleted