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?
 
Patrick MatthewsConnect With a Mentor 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

Open in new window

0
 
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, SyedConnect With a Mentor 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
KirkmanCommented:

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
 
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
 
KirkmanConnect With a Mentor Commented:
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
 
KirkmanCommented:
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
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.