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

Sub deltotal()

Range("b1").Activate
Set Var = ActiveCell
For x = 1 To 65536
On Error GoTo skp:
g = Application.WorksheetFunction.Search("total", Var)
Set Var = ActiveCell.Offset(1, 0)
ActiveCell.EntireRow.Select
Selection.Delete
GoTo newvar
skp:
Var.Offset(1, 0).Activate
Set Var = ActiveCell
Resume
newvar:
Var.Activate
Next
End Sub


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=376890

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 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 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 ,
"Jim Thomlinson" 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 ,
"Jim Thomlinson" 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

--
MAYDA
-----------------------------------------------------------------------
MAYDAY's Profile: http://www.excelforum.com/member.php...fo&userid=1354
View this thread: http://www.excelforum.com/showthread.php?threadid=37689

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.