Multiple Column Array to delete Row

[rkol297]

Hello,
I need code that will look at each of these columns in a single row: C,D,E,F,G,H And within those columns in the same row look for an array of values for example: X,Y,Z,A,B,C...etc. If any of the values X,Y,Z,A,B,C...etc are present in any of the columns C,D,E,F,G,H do not delete the row. But if NONE of the values X,Y,Z,A,B,C...etc are present in C,D,E,F,G,H then delete entire row this code will need to find the last row used and cycle through all active rows. Any help would be greatly appreciated.
Thank you in advance!

 

[rkol297]

Here is code that I wrote to look at a single column "E" to delete row if the value in the array are present in that row but I need to figure out how to expand this to look at all rows C,D,E,F,G,H and not delete the row if values in the array are present rather delete row if the values are not in the array.

   Dim CelMEDCOVF As Range, RngMEDCOVF As Range, iMEDCOVF As Long
    Set RngMEDCOVF = Columns("E").SpecialCells(xlConstants, xlTextValues)
    For iMEDCOVF = RngMEDCOVF.Count To 2 Step -1
     If RngMEDCOVF(iMEDCOVF).Value = "ARAL, BERT, GLSA, ZEMA" _
            Then RngMEDCOVF(iMEDCOVF).EntireRow.Delete
    Next iMEDCOVF

 

[novel]

This should do what you want.

Assumptions:
1. you want to run it on a specific sheet
2. there is at least one column that is always filled with data

if either of these are not true, then some minor changes are needed. but the loop does delete rows based on if a column meets a condition or not

Sub ColCheck()
Const shtName = "Sheet1" 'the name of the sheet you want this to run on
Const colFilled = 1 'column number for the column that has all its rows filled
Dim marks As Variant 'the values you want to check each column against
Dim lastrow As Integer 'last row for column 1
Dim flag As Boolean 'flags if columns have marks




marks = Array("X", "Y", "Z", "A", "B", "C")


With Sheets(shtName)
    lastrow = .Cells(Rows.Count, colFilled).End(xlUp).Row
    
    For r = lastrow To 2 Step -1
        flag = False
        checks = .Range(.Cells(r, 3), .Cells(r, 8))
        For Each c In checks
            If flag = True Then
                Exit For
            ElseIf Not IsError(Application.Match(c, marks, 0)) Then
                flag = True
            End If
        Next c
        If flag = False Then Cells(r, colFilled).EntireRow.Delete
    Next r
    
End With


End Sub

Note: columns 3 and 8 are C and H. if you want those to be dynamic would need to change (e.g., match headers, if they exist and are unique, and get column number).