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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
:)
Patrick
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I guess I should have used the word blank instead of "spaces". Sorry. If the cells appear to be BLANK, delete the row.
How to use "blank" if you use TRIM ALL??
If use Trim, your all "blank" cell will be empty, and the script is correct
If use Trim, your all "blank" cell will be empty, and the script is correct
dbfromnewjersey,
have you tried any of the proposed solutions?
have you tried any of the proposed solutions?
[...]
>>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?
:)