Automatically Delete Rows that contain a certain word?
Posted by David Morris on January 09, 2002 8:28 AM
I just had someone on the board help me to highlight all the *Total* rows.
Using this VB script:
Sub Change()
Dim Cnt As Integer
For Cnt = 1 To 500
If UCase(Range("B" & Cnt)) = "*TOTAL*" Then
Range("A" & Cnt, "N" & Cnt).Interior.ColorIndex = 4
End If
Next
End Sub
Now, Is there a way to delete rows that contain the word *Total* in column A, but not column b? I am trying to automate some reports that require a ton of formatting each day.
If you would like me to email you a copy of the report to see what I am talking about - please email me.
Thanks,
David
Posted by Tom Dickinson on January 09, 2002 9:47 AM
Posted by Tom Dickinson on January 09, 2002 9:53 AM
Add these lines
Add the following to your code right after the line "For Cnt = 1 to 500":
If UCase(Range("A" & Cnt)) = "TOTAL" Then
Range("A" & 3).EntireRow.Delete
End If
Its me again.
Posted by Nate Big Guns Oliver on January 09, 2002 9:59 AM
Two more methods
These should work too:
sub delete1()
Range("a1:a500").Select
For Each cell In Selection
If cell.Value = "*TOTAL*" Then
Selection.EntireRow.Delete
End If
Next cell
Range("a1").Select
end sub
sub delete2()
Range("a1:a20000").Select
For Each cell In Selection
If cell.Value = "*TOTAL*" Then
cell.ClearContents
End If
Next cell
Range("a1:a20000").Select
Selection.SpecialCellsxlCellTypeBlanks).EntireRow.Delete
end sub
Cheers!
Posted by Nate Big Guns Oliver on January 09, 2002 10:01 AM
Correction on Delete2()
It seems I was missing a ( on the second one regarding the 'special' cells. Here's the correction:
sub delete2()
Range("a1:a20000").Select
For Each cell In Selection
If cell.Value = "*TOTAL*" Then
cell.ClearContents
End If
Next cell
Range("a1:a20000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end sub
Cheers!
Posted by Nate Oliver on January 09, 2002 2:49 PM
Sub Delete1() Needs a change
Sorry, had to change selection.EntireRow.delete
to cell.EntireRow.delete. This should work now, my apologies
Sub delete1()
Range("a1:a500").Select
For Each cell In Selection
If cell.Value = "*TOTAL*" Then
cell.EntireRow.delete
End If
Next cell
Range("a1").Select
End Sub
Posted by David Morris on January 10, 2002 6:45 AM
Re: Add these lines
Tom,
OK, you are the man. I only have one problem. When I run the macro to delete the rows that have *Total* in coumn A - it only deletes every other line.I have the MACRO assigned to a button and I have to hit it several times to work my way through all the totals. Any clue?
Thanks agin for all your help. Feel free to email me directly if you would like a copy of one of these reports to see what I am talking about.
David
david.morris@target.com
