IF AND with Multiple OR values - Code doesnt do anything

[Vaghela]

Hi All

My First question post on this forum. I have searched extensively however i was not able to get anywhere with the following statement. My aim is to delete rows if conditions for Column F and G are not met.

If column F has a certain value and Column G doesnt have defined 3 values I would like the row to be deleted.


If Cells(i, 6) = "AVALUE" And Cells(i, 7) <> "I7" Or Cells(i, 7) <> "I8" Or Cells(i, 7) <> "I9" Then Rows(i & ":" & i).EntireRow.Delete


Any help is appreciated.

 

[JackDanIce]

Welcome to the board.

What is the name of the sheet data is on?
What row number does the data start on?
What row number does the data end on?

Please paste all of the existing code, if you have any.

 

[Kaper]

The important tip is to note that if you delete row (say) 11, then this row, which was so far "row 12" will be new row 11, etc.

So run your deleting row_by_row process in bottom-top direction, like:

for i = 100 to 2 step -1 'of course your bottom and top boundaries
  If Cells(i, 6) = "AVALUE" And Cells(i, 7) <> "I7" Then Rows(i).Delete
next i

note also, that the condition for IF in your original post will alwaus be true. because if cells(i,7) is = "I8" (not delete as shall be <>) then next one if cells(i,7) is <> "IG" so you will delete all rows in a range.

Probably (?) you wanted to delete rows wher in column 6 (F) there is AVALUE" and in column7 (G) there is anything but I7, I8, I9

it shall be written (remember - going bottom-top direction:

If Cells(i, 6) = "AVALUE" And not (Cells(i, 7) = "I7" Or Cells(i, 7) = "I8" Or Cells(i, 7) = "I9") Then Rows(i).Delete

 

[Vaghela]

Thanks Kaper, for going out the way to explain why For (bottom top) is used most often.

I shall incorporate both the codes and revert back if i face any issue.

 

[Vaghela]

Hi Jack

I am still in process of writing the code but was stuck on how Multiple OR values can be used. I shall revert back on how i get on with the code

 

[JackDanIce]

Potentially, slower to evaluate to delete single rows for each loop iteration, here's a suggestion that doesn't reverse loop and removes the required rows in a single operation. Adjust parts in blue as required:

Sub Unique_Filter()

    Dim x   As Long
    Dim LR  As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.count, 6).End(xlUp).row
    For x = 2 To LR
        If Cells(x, 6).Value = "TEST" And InStr("I7|I8|I9", Cells(x, 7)) = 0 Then Cells(x, 6).ClearContents
    Next x
    
    Cells(1, 6).Resize(LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                
    Application.ScreenUpdating = True
            
End Sub

 

[Vaghela]

Jack/Kaper

thanks for you assistance, Helped alot in completing the part of the project.

 

[JackDanIce]

You're welcome

 

[Kaper]

Glad to hear so