Delete value if found in cell.
 

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.

Delete value if found in cell.
 

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.

Delete value if found in cell non-case sensitive
 

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.

Delete value if found in cell.
 

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

Delete value if found in cell.
 

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

Delete value if found in cell.
 

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

Delete value if found in cell.
 

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?

Delete value if found in cell.
 

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

Delete value if found in cell.
 

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.

Delete value if found in cell.
 

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

Delete value if found in cell.
 

On Mar 26, 2:29*am, Ron Rosenfeld wrote:
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- Hide quoted text -

- Show quoted text -

Yes Ron,

I have used this approach.

Dim TestRow As Range

LastCol = Selection.Columns.Count
For r = Selection.Rows.Count To 1 Step -1
Set TestRow = Range(Cells(r, 1), Cells(r, LastCol))
TestRow.Select
For Each c In TestRow
If c.Value Like "----*" Then
c.EntireRow.Delete
ElseIf UCase(c.Value) Like UCase("Total*") Then
c.EntireRow.Delete
ElseIf c.Value Like "====*" Then
c.EntireRow.Delete
End If
Next
Next

Posted by Per(Thanks)

I got the result I wanted.

I have checked your code as well.
With adjustment, it also worked. Thx.


You recon to use bottom up in all cases rather than top down or does
it vary as per requirement?

Thanks once again.

Delete value if found in cell.
 

On Tue, 25 Mar 2008 14:44:52 -0700 (PDT), Sinner wrote:

You recon to use bottom up in all cases rather than top down or does
it vary as per requirement?

Use bottom up whenever you will be deleting rows.



Thanks once again.

You're welcome. Glad we got things to work.
--ron