Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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


Check out our Excel VBA Resources

Re: Automatically Delete Rows that contain a certain word?

Posted by Tom Dickinson on January 09, 2002 9:47 AM


Add these lines

Posted by Tom Dickinson on January 09, 2002 9:53 AM
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.


Two more methods

Posted by Nate Big Guns Oliver on January 09, 2002 9:59 AM
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!


Correction on Delete2()

Posted by Nate Big Guns Oliver on January 09, 2002 10:01 AM
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!


Sub Delete1() Needs a change

Posted by Nate Oliver on January 09, 2002 2:49 PM
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


Re: Add these lines

Posted by David Morris on January 10, 2002 6:45 AM
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.