[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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!!!
0
leezac
Asked:
leezac
  • 3
  • 2
1 Solution
 
FlysterCommented:
Exactly how does your data appear in column B? In your logic, should it be column B has "A" AND "F"?

Flyster
0
 
FaustulusCommented:
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now