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

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

Patrick MatthewsCommented:
>>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
Saqib Husain, SyedEngineerCommented:
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
Ionut GIT 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

Open in new window

0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Patrick MatthewsCommented:
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

Open in new window

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.

Start your 7-day free trial
Patrick MatthewsCommented:
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

Open in new window


:)

Patrick
0
Ionut GIT 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

Open in new window

0
dbfromnewjerseyAuthor 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
Ionut GIT 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
Saqib Husain, SyedEngineerCommented:
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.