# Excel Macro To Delete Rows Based On Certain Criteria

I need to check every row containing data in my Excel worksheet. I first need to TRIM columns 4, 5 and 6.  Then, for each row:

if the value in Column 4 = 'X'  delete the entire row
OR
if the values in Columns 5 and 6 both equal spaces, then delete the entire row

Only delete the row if both column 5 AND column 6 are empty  or if column 4 = 'X'

Thank you

Asked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
>>I first need to TRIM columns 4, 5 and 6.
[...]
>>if the values in Columns 5 and 6 both equal spaces, then delete the entire row

If you trim Cols 5 & 6, then they cannot equal "spaces", whatever that means.  That also contradicts:

>>Only delete the row if both column 5 AND column 6 are empty  or if column 4 = 'X'

So, which is it?

:)
0
EngineerCommented:
Sub delrows()
Set dr = ActiveSheet.UsedRange
For i = dr.Row + dr.Rows.Count To dr.Row Step -1
Cells(i, 4) = Trim(Cells(i, 4))
Cells(i, 5) = Trim(Cells(i, 5))
Cells(i, 6) = Trim(Cells(i, 6))
If Cells(i, 4) = "X" Or (Cells(i, 4) = "" And Cells(i, 4) = "") Then
Cells(i, 4).EntireRow.Delete
End If
Next i
End Sub
0
IT ManagerCommented:

``````Sub DeleteMyRow()

LastRow = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Range(Cells(1, 4), Cells(LastRow, 6)).Select
For Each cell In Selection.Cells
cell.Value = Trim(cell)
Next cell

For i = 1 To LastRow
If (Cells(i, 4) = "x") Or (Cells(i, 5) = "" And Cells(i, 6) = "") Then
Cells(i, 4).Select
ActiveCell.EntireRow.Delete
If Cells(i, 4) <> "" Or Cells(i, 5) <> "" Or Cells(i, 6) <> "" Then i = i - 1
End If
Next i

End Sub
``````
0
Commented:
An autofilter based approach.  If you have >8196 rows, it may need a tweak due to a limitation in SpecialCells:

``````Sub KillRows()

Dim LastR As Long, LastC As Long

With ActiveSheet
LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastC + 1) = "ZZZ"
.Range(.Cells(2, LastC + 1), .Cells(LastR, LastC + 1)).Formula = _
"=IF(OR(D2=""X"",AND(TRIM(E2)="""",TRIM(F2)="""")),1,0)"
.[A1].AutoFilter
.[A1].AutoFilter Field:=(LastC + 1), Criteria1:=1, Operator:=xlAnd
On Error Resume Next
.Range(.Cells(2, LastC + 1), .Cells(LastR, LastC + 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
.[A1].AutoFilter
.Cells(1, LastC + 1).EntireColumn.Delete
End With

End Sub
``````
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Kirkman,

Two notes:

1) By default, VBA string comparisons are case sensitive, so unless you have a module level declaration of Option Compare Text,

Cells(i, 4) = "x")

will reject a value of "X".  You can fix this with:

LCase(Cells(i, 4)) = "x")

2) Rather than adjusting the value of i within the loop, you might want to consider looping backward:

``````For i = LastRow To 1 Step -1
If (Cells(i, 4) = "x") Or (Cells(i, 5) = "" And Cells(i, 6) = "") Then
Cells(i, 4).EntireRow.Delete
End If
Next i
``````

:)

Patrick
0
IT ManagerCommented:
ok.. my mistake of x.. you can use X
and second, i pu that  If Cells(i, 4) <> "" Or Cells(i, 5) <> "" Or Cells(i, 6) <> "" Then i = i - 1 to spoting loop

``````Sub DeleteMyRow()

LastRow = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Range(Cells(1, 4), Cells(LastRow, 6)).Select
For Each cell In Selection.Cells
cell.Value = Trim(cell)
Next cell

For i = 1 To LastRow
If (Cells(i, 4) = "X") Or (Cells(i, 5) = "" And Cells(i, 6) = "") Then
Cells(i, 4).Select
ActiveCell.EntireRow.Delete
If Cells(i, 4) <> "" Or Cells(i, 5) <> "" Or Cells(i, 6) <> "" Then i = i - 1
End If
Next i

End Sub
``````
0
Author Commented:
I guess I should have used the word blank instead of "spaces". Sorry.  If the cells appear to be BLANK, delete the row.
0
IT ManagerCommented:
How to use "blank" if you use TRIM ALL??
If use Trim, your all "blank" cell will be empty, and the script is correct
0
EngineerCommented:
dbfromnewjersey,

have you tried any of the proposed solutions?
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.