Delete Row

[Tempy]

Hi, the code below is what i found from searching and it was from Bob, I
need to convert it to find the word "Total" and delete that row with
total in it, But i am not too sure how to modify it ?

Could somebody please help?

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 6500 And Cells(i, "A").Value > 5599
Then
Rows(i).Delete
End If
Next i


Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[FSt1]

hi,
If you word "total is all in the same column then you can use this code.
otherwise you might have a problem. it could be used but you may have to run
it for each column the word "total appears.
in this line,
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
the letter "A" denote column A. the letter i denotes the row number.
change the "A" to the column that the word "total" apears in.
change this line...
If Cells(i, "A").Value < 6500 And Cells(i, "A").Value > 5599 Then
to this
If Cells(1,"yourcolumn").value = "Totals" then

if that doesn't work for you, try something like this...
Sub mac1FindAll()
Dim c As String
Dim sh As Worksheet
Dim rng As Range
Dim cAddr As String

'c = InputBox("Enter item to delete") 'Optional method
c="Totals"
For Each sh In ActiveWorkbook.Worksheets
If c <> "" Then
Set rng = Nothing
Set rng = sh.Range("A1:IV65000").Find(what:=c, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
cAddr = rng.Address
sh.Activate
rng.Select
Selection.entirerow.delete shift:=xlup
Do
Set rng = Range("a1:IV65000").FindNext(rng)
If rng.Address = cAddr Then
MsgBox ("no more " & c & " 's were found.")
Exit Do
Else
rng.Select
Selection.entirerow.delete shift:=xlup
End If
Loop Until rng.Address = cAddr
End If
Next sh
If rng Is Nothing Then
MsgBox c & " was Not found"
End If
End Sub

regards
FSt1

 

[Otto Moehrbach]

This should do it. Post back if you need more. HTH Otto
Sub DeleteTotal()
Dim i As Long
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = "Total" Then
Rows(i).Delete
End If
Next i
End Sub

 

[Tom Ogilvy]

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If lcase(Cells(i, "A").Value) = "total" Then
Rows(i).Delete
End If
Next i

if the cell will contain more than the word total, but you want to delete it
if it does contain the word total as part of a larger string: (it will work
if total is in the cell by itself as well)

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Instr(1,Cells(i, "A").Value),"total",vbTextCompare) Then
Rows(i).Delete
End If
Next i

 

[Tempy]

FSt1,
Thank you so much for your answer and explanation, it helps newbies like
me to undertstand better

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tempy]

Thanks Tom, that helps me a lot. I really want to compliment all that
help for a really great job, thank you.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***