Delete rows based on values in columns

Can someone please help with this - I can't figure how the logic will work.

I have on sheet1 where I need to delete rows for A1 to end of last used row where

if column B has "A" or "F" to delete the rows where B = "F" else
if column B has "F" but not "A" do not delete rows.....


Thanks in advance!!!
leezacAsked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
This code will delete all rows where column B contains both, "F" and "A".
Option Explicit

Sub DeleteFA()

    Const FirstDataRow As Long = 1
    Dim R As Long
    Dim CellVal As String
    
    Application.ScreenUpdating = False
    With ActiveSheet.Columns(2)         ' 2 = column B
        For R = .Cells(.Rows.Count).End(xlUp).Row To FirstDataRow Step -1
            CellVal = .Cells(R).Value
            If InStr(1, CellVal, "F", vbTextCompare) And _
               InStr(1, CellVal, "A", vbTextCompare) > 0 Then
               .Cells(R).EntireRow.Delete
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

It will not delete rows where only "F" is found. The code will find "F" as well as "f", "A" as well as "a", and the two characters can be in any sequence, interspersed with other characters or numbers.
0
 
FlysterCommented:
Exactly how does your data appear in column B? In your logic, should it be column B has "A" AND "F"?

Flyster
0
 
leezacAuthor Commented:
Thanks so much but I need to keep rows that do NOT contain F or A which will be blank.

Logic is to delete rows with "F" in column B if there is an "A" in column B.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
leezacAuthor Commented:
I think I have part of the solution.  
If there is an "A" and "F" in column "B" delete the F rows

else if there is an "F" only and NOT "A" - do not delete the "F" rows

May need to add another column with true and false logic.  Not sure what to do....

Sub Delete_Rows()
Dim r As Long
 Sheets("Sheet3").Select

For r = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
If UCase(Left(Cells(r, "B").Text, 8)) = "F" Then Rows(r).Delete
Next r
End Sub
0
 
leezacAuthor Commented:
I am going to have to repost and ask another way I think.
0
 
FaustulusCommented:
Perhaps the best way is to test my code and tell me what it does wrong. The test is all set up, and you might simply say which rows (by number) were deleted and should not have been and which rows should have been deleted but were not.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.