On Tue, 25 Mar 2008 09:28:36 -0700 (PDT), Sinner wrote:

On Mar 25, 8:43*pm, Ron Rosenfeld wrote:
On Tue, 25 Mar 2008 07:20:05 -0700 (PDT), Sinner wrote:
On Mar 25, 6:51*pm, Ron Rosenfeld wrote:
On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner wrote:
This is where i need a fix

For Each c In Range("A1").CurrentRegion
* *If c.Value Like "----*" Or _
* *c.Value Like "====*" Or _
* *UCase(c.Value) Like "*TOTAL*" Then ' Case sensitive

* * * *c.EntireRow.Delete

* *End If

Using UCASE will work to make your TOTAL case insensitive.

What I gave was another method:

"You could precede your module with
Option Compare Text"

So, as I showed in my example, prior to your SUB statement, you place the line:

Option Compare Text

--ron

When I use "*TOTAL*", it is deleting only word TOTAL.
When I use "*TOTAL *", it is deleting TOTAL PAYABLE, GRAND TOTAL etc.
and not TOTAL only.

Any ideas?

I'm not sure I understand what you want to do.

In your first post, and subsequent posts, it appears as if you want to delete
the entire row if "TOTAL" is found in 'Range("A1").CurrentRegion' *but were
having a problem because of your code being case-sensitive.

You were given two different solutions for that.

If you want to delete the entire row if you find ONLY the word TOTAL in some
cell in 'Range("A1").CurrentRegion', then omit the "*"'s.

If you just want to delete the word "TOTAL" from the cell, take a look at the
Replace method.

If you want to do something else, you will need to be more specific.
--ron- Hide quoted text -

- Show quoted text -

Ronsenfeld I'm importing a sheet with some data.
After the import I want to delete rows if a cell contains word total
in it. it can be only total, total payable, grand total, total ABC,
TOTAL etc.


Now I have used total wihtout "*"s

For Each c In Range("A1").CurrentRegion
If c.Value Like "----*" Or _
c.Value Like "====*" Or _
UCase(c.Value) Like "TOTAL" Then ' Case sensitive

c.EntireRow.Delete

End If
Next

Still I can see total payable in columnE.

OK, I see what is happening now.

You MUST have the "*" if you want to delete lines where the cells contain TOTAL

In other words ... Like "*TOTAL*" is correct.

The reason you are seeing certain lines where a cell contains TOTAL being left
behind is because your

For each c in ...

next c

loop is not processing all the lines.

For example, let us say you have

A1: Type
A2: Total
A3: Total Payable
A4: Something Else


Row 1 gets retained
Row 2 gets deleted

Once row 2 gets deleted, row 3 moves up to row 2. BUT "next c" no longer
refers to row 2 -- rather it refers to row 3. So the row that originally
contained TOTAL Payable never got processed.


In order to avoid this, you must process your lines from the bottom up, rather
than from the top down.

Assuming your Total might be in column A, then something like:

=================
Option Explicit
Option Compare Text
Sub foo()
Dim c As Range
Dim lLastRow As Long
Dim rw As Long

Set c = Range("A1")
lLastRow = c.End(xlDown).Row

For rw = lLastRow To 1 Step -1
If Cells(rw, 1).Value Like "*TOTAL*" Then
Cells(rw, 1).EntireRow.Delete
End If
Next rw
End Sub
=======================

If not in column A, change the columnindex argument for the Cells property to
reflect the column to check.

If TOTAL might be in more than one column, then set up a sub loop to check each
column as you go up the rows.
--ron