Macro deletes all data rather than just intended rows

[hip2b2]

The following VBA code is intended to delete the Rows in Sheet2 where Field 12 contains the string ABC (or ABC and anything else), this works as intended. What I have belatedly discovered is that if Field 12 does not contain any instance of ABC all the data in Sheet2 below Row 6 is deleted.

Where have I messed up?

Thanks in advance.

hip

Set h = Sheets("For Sheet2")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With h.Range("A6:L" & lr)
    
    .AutoFilter Field:=12, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABC
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12

 

[Michael M]

See Post#9

 

[hip2b2]

No nothing I see references Col W. Delete criteria is in Field 12 (Col L)

 

[Michael M]

But there is nothing in col L ??

 

[hip2b2]

First let me apologize. I have asked many question in the past but have never messed up the asking as badly as I have this time.

Data is copied from Sheet1 to Sheet2. Filter criteria in Sheet2 is contained in Col L. Where the filter term is met the row is be deleted. So far so good!!!

Problem:
If the filter term does not exist (in the case below "Closed", the all the data on sheet 2 (below row 6) are deleted. That is what I am trying to correct.


Set h = Sheets("For Sheet2")
If h.AutoFilterMode Then h.AutoFilterMode = False
lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With h.Range("A6:L" & lr)

.AutoFilter Field:=12, Criteria1:="Closed" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: Closed
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=12

 

[Michael M]

Can you upload the workbook to a hosting site...dropbox for instance..then post the link back here please ?

 

[hip2b2]

This should do it:

File on MEGA

mega.nz

The macro will delete the rows where "Open" or "Closed" are in Field 12 (Col L). If either of these are missing the entire database is deleted.

Again, thanks for sticking with my problem.

 

[Michael M]

Try it this way

Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
 For r = lr To 7 Step -1
    If Cells(r, 12) = "Closed" Or Cells(r, 12) = "Open" Then
      Rows(r).Delete
    End If
 Next r
Application.ScreenUpdating = True
End Sub

 

[hip2b2]

Try it this way

Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
 For r = lr To 7 Step -1
    If Cells(r, 12) = "Closed" Or Cells(r, 12) = "Open" Then
      Rows(r).Delete
    End If
 Next r
Application.ScreenUpdating = True
End Sub

Funny how the filter mode almost works; odd that there isn't a "switch" of one sort or another to only delete where the text exists as opposed to doing what it does.

Thanks, for the new macro it looks like it will do what I need to do, I'll test it tomorrow. BTW, would this work if the cells contain the specied text along with other text For example "Open in the morning", or is it restricted to the exact text string entered in the macro?

Regards

hip

 

[Michael M]

The current code is restricted to "Open" or "Closed". If you wanted to expand it use

Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
 For r = lr To 7 Step -1
  If InStr(Cells(r, 12), "Closed") Or InStr(Cells(r, 12), "Open") Then
      Rows(r).Delete
    End If
 Next r
Application.ScreenUpdating = True
End Sub

 

[hip2b2]

Thank you