Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete a row if strong NOT found.... | Excel Discussion (Misc queries) | |||
Delete a cell when found | Excel Programming | |||
VLOOKUP and delete row if found | Excel Programming | |||
How to delete values of a cell if it is found in another coloumn | Excel Worksheet Functions | |||
Further help on delete criteria found | Excel Programming |