Delete rows with specific text

Hi,

Can someone please provide the code that will delete all rows in column B that contain a cell with the word 'Total'?

Thanks

One way:

Public Sub DeleteRows()
Const sLOOK As String = "Total"
Dim rFound As Range
Dim rDelete As Range
Dim sFoundAddr As String
Set rFound = Columns(2).Find( _
What:=sLOOK, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rFound Is Nothing Then
Set rDelete = rFound
sFoundAddr = rFound.Address
Do
Set rFound = Columns(2).FindNext(After:=rFound)
Set rDelete = Union(rDelete, rFound)
Loop Until rFound.Address = sFoundAddr
rDelete.EntireRow.Delete
End If
End Sub





In article <MAYDAY.1q80uh_1118088325.951@excelforum-nospam.com>,
MAYDAY <MAYDAY.1q80uh_1118088325.951@excelforum-nospam.com> wrote:

> Can someone please provide the code that will delete all rows in column
> B that contain a cell with the word 'Total'?

Here is some code that is a little faster since it does not loop through each
cell...

Public Sub RemoveTotals()
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns(2)

Set rngFound = rngToSearch.Find("Total")
If rngFound Is Nothing Then
MsgBox "No Totals Found"
Else
Do
rngFound.EntireRow.Delete
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
End Sub
--
HTH...

Jim Thomlinson


"MAYDAY" wrote:

>
> Hi,
>
> Can someone please provide the code that will delete all rows in column
> B that contain a cell with the word 'Total'?
>
> Thanks
>
>
> --
> MAYDAY
> ------------------------------------------------------------------------
> MAYDAY's Profile: http://www.excelforum.com/member.php...o&userid=13548
> View this thread: http://www.excelforum.com/showthread...hreadid=376890
>
>

How much faster is it to Union everthing into one big range and delete that
at the end as you have done as opposed to deleteing as you go as my code has
done. Have you ever checked? (Note to MayDay... Use this code it should be
the most efficient)
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

> One way:
>
> Public Sub DeleteRows()
> Const sLOOK As String = "Total"
> Dim rFound As Range
> Dim rDelete As Range
> Dim sFoundAddr As String
> Set rFound = Columns(2).Find( _
> What:=sLOOK, _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> MatchCase:=False)
> If Not rFound Is Nothing Then
> Set rDelete = rFound
> sFoundAddr = rFound.Address
> Do
> Set rFound = Columns(2).FindNext(After:=rFound)
> Set rDelete = Union(rDelete, rFound)
> Loop Until rFound.Address = sFoundAddr
> rDelete.EntireRow.Delete
> End If
> End Sub
>
>
>
>
>
> In article <MAYDAY.1q80uh_1118088325.951@excelforum-nospam.com>,
> MAYDAY <MAYDAY.1q80uh_1118088325.951@excelforum-nospam.com> wrote:
>
> > Can someone please provide the code that will delete all rows in column
> > B that contain a cell with the word 'Total'?
>

Since deleting every row requires reindexing all the remaining rows, and
deleting all at once apparently doesn't, it's variable. The gain in
efficiency should increase in proportion to both hits and total rows. In
a (very) informal test with 16000 rows and 1000 hits, deleting each row
took about 6 times as long as deleting all at once.


In article <0837CBCA-512E-4B10-8DA9-6BE0DB9416C4@microsoft.com>,
"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote:

> How much faster is it to Union everthing into one big range and delete that
> at the end as you have done as opposed to deleteing as you go as my code has
> done. Have you ever checked? (Note to MayDay... Use this code it should be
> the most efficient)

I knew it would be more efficient but that is a lot. I will go the extra
distance in the future to create one big range to delete at the end...
Thanks... It never ceases to amaze me how much info you can pick up from this
forum!
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

> Since deleting every row requires reindexing all the remaining rows, and
> deleting all at once apparently doesn't, it's variable. The gain in
> efficiency should increase in proportion to both hits and total rows. In
> a (very) informal test with 16000 rows and 1000 hits, deleting each row
> took about 6 times as long as deleting all at once.
>
>
> In article <0837CBCA-512E-4B10-8DA9-6BE0DB9416C4@microsoft.com>,
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote:
>
> > How much faster is it to Union everthing into one big range and delete that
> > at the end as you have done as opposed to deleteing as you go as my code has
> > done. Have you ever checked? (Note to MayDay... Use this code it should be
> > the most efficient)
>

Thank you all!

> Here is some code that is a little faster since it does not loop through
each
> cell...


I tested that on 10,000 rows where every other one said "Total". You're
right, it's a lot faster than the other.

the way I do that is by passing a formula into a routine... (acknowledged,
my method would not work if there were not 1 free column at the far right of
the spreadsheet).

the del_rows subroutine accepts any formula... if it meets the condition, it
evaluates to "", if it doesn't, it evaluates to 1.

the rountine puts the formula in, copies it to the end of the data range,
sorts by the result, and deletes the ones that met the delete condition.

In the same test, that method took about half a second.