Hi,


I want to delete work total if found in the sheet.
The problem with below is that it is only deleting case sensitive
values.

ElseIf c.Value Like "*TOTAL*" Then ' Case sensitive
c.EntireRow.Delete

Any fix would be appreciated.

Maybe

UCase(c.Value) Like "*TOTAL*" Then

Mike

"Sinner" wrote:

> Hi,
>
>
> I want to delete work total if found in the sheet.
> The problem with below is that it is only deleting case sensitive
> values.
>
> ElseIf c.Value Like "*TOTAL*" Then ' Case sensitive
> c.EntireRow.Delete
>
> Any fix would be appreciated.
>

On Mar 25, 5:41*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> Maybe
>
> UCase(c.Value) Like "*TOTAL*" Then
>
> Mike
>
>
>
> "Sinner" wrote:
> > Hi,
>
> > I want to delete work total if found in the sheet.
> > The problem with below is that it is only deleting case sensitive
> > values.
>
> > ElseIf c.Value Like "*TOTAL*" Then ' Case sensitive
> > * * * * c.EntireRow.Delete
>
> > Any fix would be appreciated.- Hide quoted text -
>
> - Show quoted text -

Mike,
It is not deleting total payables, Grand Total etc.

On Tue, 25 Mar 2008 05:30:57 -0700 (PDT), Sinner <(E-Mail Removed)> wrote:

>Hi,
>
>
>I want to delete work total if found in the sheet.
>The problem with below is that it is only deleting case sensitive
>values.
>
>ElseIf c.Value Like "*TOTAL*" Then ' Case sensitive
> c.EntireRow.Delete
>
>Any fix would be appreciated.

You could precede your module with

Option Compare Text

e.g.:

Option Explicit
Option Compare Text
Sub foo()
Const s1 As String = "TOTAL"
Const s2 As String = "total"

Debug.Print IIf(s1 Like "TOTAL", True, False)
Debug.Print IIf(s2 Like "TOTAL", True, False)
End Sub
--ron

On Mar 25, 6:10*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Tue, 25 Mar 2008 05:30:57 -0700 (PDT), Sinner <ims...@gmail.com> wrote:
> >Hi,
>
> >I want to delete work total if found in the sheet.
> >The problem with below is that it is only deleting case sensitive
> >values.
>
> >ElseIf c.Value Like "*TOTAL*" Then ' Case sensitive
> > * * * *c.EntireRow.Delete
>
> >Any fix would be appreciated.
>
> You could precede your module with
>
> Option Compare Text
>
> e.g.:
>
> Option Explicit
> Option Compare Text
> Sub foo()
> Const s1 As String = "TOTAL"
> Const s2 As String = "total"
>
> Debug.Print IIf(s1 Like "TOTAL", True, False)
> Debug.Print IIf(s2 Like "TOTAL", True, False)
> End Sub
> --ron

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
Next

On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner <(E-Mail Removed)> 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

On Mar 25, 6:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner <ims...@gmail.com> 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?

On Tue, 25 Mar 2008 07:20:05 -0700 (PDT), Sinner <(E-Mail Removed)> wrote:

>On Mar 25, 6:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner <ims...@gmail.com> 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

On Mar 25, 8:43*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Tue, 25 Mar 2008 07:20:05 -0700 (PDT), Sinner <ims...@gmail.com> wrote:
> >On Mar 25, 6:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> >> On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner <ims...@gmail.com> 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.

On Tue, 25 Mar 2008 09:28:36 -0700 (PDT), Sinner <(E-Mail Removed)> wrote:

>On Mar 25, 8:43*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> On Tue, 25 Mar 2008 07:20:05 -0700 (PDT), Sinner <ims...@gmail.com> wrote:
>> >On Mar 25, 6:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> >> On Tue, 25 Mar 2008 06:34:18 -0700 (PDT), Sinner <ims...@gmail.com> 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