Delete value if found in cell.

  • Thread starter Thread starter Sinner
  • Start date Start date
S

Sinner

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.
 
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.
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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?
 
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?
Click to expand...

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
 
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 -
Click to expand...

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.
 
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.
Click to expand...

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
 
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 propertyto
reflect the column to check.

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

- Show quoted text -
Click to expand...

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.
 
You recon to use bottom up in all cases rather than top down or does
it vary as per requirement?
Click to expand...

Use bottom up whenever you will be deleting rows.

Thanks once again.
Click to expand...

You're welcome. Glad we got things to work.
--ron
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How put Array in VBA 3
Run Macro If Cell have "x" value 0
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
For/Next Is Not Looping - Only Deletes First Instance 2
Delete Entire Row 4
Deleting Rows Problem 5
macro hangs if no data is found 6
ClearContents if #VALUE! found in cell 2

Back
Top