Solved

# Delete rows based on values in columns

Posted on 2013-11-12
291 Views

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.....

0
Question by:leezac
• 3
• 2

LVL 22

Expert Comment

Exactly how does your data appear in column B? In your logic, should it be column B has "A" AND "F"?

Flyster
0

LVL 14

Accepted Solution

Faustulus earned 500 total points
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
``````
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

Author Comment

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

Author Comment

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

Author Closing Comment

I am going to have to repost and ask another way I think.
0

LVL 14

Expert Comment

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

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…