Exception to a rule within a Macro

[Livin404]

Greetings, I have the macro below which works perfectly in removing rows if a column contains specific text. I would like to add an exception to the rule. If I have "B737", "A310", "B767" , or "B777" in Column C I would like those rows NOT to be removed if otherwise would be removed based on the macro: Thank you,

Sub Delete_locals()
With ActiveSheet
.AutoFilterMode = False
With Range("H1", Range("H" & Rows.Count).End(xlUp))
.AutoFilter 1, "*CHS*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("H1", Range("H" & Rows.Count).End(xlUp))
.AutoFilter 1, "*777*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

 

[Peter_SSs]

you do not want Rows.Count at the end, you want Row.

I disagree. Range("H1", Range("H" & Rows.Count).End(xlUp)).Row would always return 1, meaning that your loop would be from 2 to 1 resulting in the loop never being executed at all.
Since the range starts at row1, at least Range("H1", Range("H" & Rows.Count).End(xlUp)).Rows.Countwould return the row number of the final row, which I assume was your intention - but not the simplest way to achieve that result.

Bug reports are more than welcome.

There is a further flaw with your code. If there are successive rows that require deletion, your code will fail to delete at least one of those rows. If deleting rows via a loop you need to work up from the bottom of the range rather than down from the top (or else decrement the row counter every time a row is deleted).

@Livin404
Wondering if you tried the Advanced Filter method?

 

[6StringJazzer]

I disagree. Range("H1", Range("H" & Rows.Count).End(xlUp)).Row would always return 1, meaning that your loop would be from 2 to 1 resulting in the loop never being executed at all.
Since the range starts at row1, at least Range("H1", Range("H" & Rows.Count).End(xlUp)).Rows.Countwould return the row number of the final row, which I assume was your intention - but not the simplest way to achieve that result.

My mistake, of course. I am accustomed to see a variation of that expression and did not read carefully.

 Cells(Rows.count, 1).End(xlUp).Row

I should probably quit while I'm behind on this one. But my offer is still open to test it if I get a file or at least a complete data set.

 

[Livin404]

Did you test that code?


Advanced Filter would also be a possibility. I have assumed A1.CurrentRegion as the data to be filtered and that column Z is available to use as a helper. Both of those of course can be changed if required.

Sub Del_data()
  Range("Z2").Formula = "=AND(OR(ISNUMBER(SEARCH(""CHS"",H2)),ISNUMBER(SEARCH(""777"",H2))),NOT(OR(C2={""B737"",""A310"",""B767"",""B777""})))"
  With Range("A1").CurrentRegion
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Z1:Z2"), Unique:=False
    .Offset(1).EntireRow.Delete
    If .Parent.FilterMode Then .Parent.ShowAllData
  End With
  Range("Z2").ClearContents
End Sub

Hello I did try your code too, it shifted my macro buttons over, I don't know why, I'm not sure what is in Z2, perhaps I need to adjust that?

 

[Livin404]

Hello everyone and thank you for the input. I'm confident it is working now. However, I saw the thread and there was an instant where a discussion when back and forth. I don't have a 1 which I saw mentioned. I've attached the finished macro. Is there something I'm missing for it seems to be doing everything properly. If so then I'll mark this has complete!

Sub Delete_locals()

Dim R As Long

For R = 2 To Range("H1", Range("H" & Rows.Count).End(xlUp)).Rows.Count

If (Cells(R, "H") Like "*CHS*" Or _
Cells(R, "H") Like "*777*") And _
Not Cells(R, "C") Like "*B737*" And _
Not Cells(R, "C") Like "*A310*" And _
Not Cells(R, "C") Like "*B767E*" And _
Not Cells(R, "C") Like "*B7772E*" Then
Rows(R).Delete
End If

Next

End Sub

 

[Livin404]

Is there any reason why it is including aircraft not in the list above? It's including a military type plane not on the list in the macro. It's not doing it every case.

 

[Livin404]

When I select a large group it will not remove all of CHS or 777 from the list. I don't understand. Thank you,

 

[Peter_SSs]

Hello I did try your code too, it shifted my macro buttons over,

There was no previous mention of macro buttons. It moved them from where to where?

it will not remove all of CHS or 777 from the list.

I already stated that.

If there are successive rows that require deletion, your code will fail to delete at least one of those rows.

Before suggesting further I would like to clarify both my question at the start of this post and ..

For the values in column C that cause a row to be retained even though column H might contain "CHS" are value like "A310" the only text that would appear in the column C cell, or might column C contain other text as well? eg "Red A310" or "B567, A310, Z65"?

Also, what does this mean? Examples? Again it describes things that have not been mentioned before.

Is there any reason why it is including aircraft not in the list above? It's including a military type plane not on the list in the macro.

 

[Livin404]

I do see my line ends with rows.count which is probably wrong after all.

There was no previous mention of macro buttons. It moved them from where to where?



I already stated that.


Before suggesting further I would like to clarify both my question at the start of this post and ..

For the values in column C that cause a row to be retained even though column H might contain "CHS" are value like "A310" the only text that would appear in the column C cell, or might column C contain other text as well? eg "Red A310" or "B567, A310, Z65"?

Also, what does this mean? Examples?

There are other types of aircraft too many to mention. But other text can appear. I just want to flight those type I mentioned.

I do see my line ends rows.count which is probably wrong after all. The other guy showed

Cells(Rows.count, 1).End(xlUp).Row

, but I don't know how that can be worked in. The code I'm using I notice now is not dropping the "777".

 

[Livin404]

The very first suggestion I had seemed to nearly work, I just need to clear our hidden values in Cell C, and just Cell C.

 

[Livin404]

Sub Delete_locals()
    Dim rngExclude As Range
   
    With ActiveSheet
        .AutoFilterMode = False
        With Range("C1", Range("C" & Rows.Count).End(xlUp))
            .AutoFilter 1, Array("B737", "A310", "B767", "B777"), Operator:=xlFilterValues
            On Error Resume Next
            Set rngExclude = .Offset(1).SpecialCells(12).EntireRow
        End With
        .AutoFilterMode = False
       
        With Range("H1", Range("H" & Rows.Count).End(xlUp))
            .AutoFilter 1, Array("*CHS*", "*777*"), Operator:=xlFilterValues
            On Error Resume Next
            rngExclude.Hidden = True
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
   
End Sub

Yours is pretty close. I think I need to clear out hidden values in Column C. I have this code I used elsewhere in my project, but I can use this one too early as it gives me another problem down the line. How Can I just target Column C and only C?

Sub remove_hidden_Values()
With ActiveSheet
For Each Cell In Range("A1", Range("X" & Rows.Count).End(xlUp))
        Cell.Value = Trim(Replace(Cell.Value, Chr(160), Chr(32)))
    Next
    End With
End Sub