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 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 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 wrote:
On Tue, 25 Mar 2008 05:30:57 -0700 (PDT), 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.

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 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 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?

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