Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find part of string & delete row
Hi all, use the code below to find "Total" to delete the row, Total is
part of the string & i am not sure how to change the code to find "Total" even if it is part of a string ? Sub DeleteTotalSuppSAP() ' ' Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If InStr(1, Cells(i, "G").Value, "total", vbTextCompare) Then Rows(i).Delete End If Next i End Sub Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find part of string & delete row
You code you show identifies cells in column G that contain the substring
total in any case (upper, lower, mixed). so your code doesn't need to be changed. -- Regards, Tom Ogilvy "Les Stout" wrote in message ... Hi all, use the code below to find "Total" to delete the row, Total is part of the string & i am not sure how to change the code to find "Total" even if it is part of a string ? Sub DeleteTotalSuppSAP() ' ' Dim i As Long Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If InStr(1, Cells(i, "G").Value, "total", vbTextCompare) Then Rows(i).Delete End If Next i End Sub Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find part of string & delete row
Hi Tom, i have tried it again and cell G11827 has the string "Total due
to supplier" in it, so i want it to delete this row, but it does not ? Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find part of string & delete row
Maybe you shouldn't be basing the lastrow on column A:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row maybe... iLastRow = Cells(Rows.Count, "G").End(xlUp).Row If column A ended in row 11, you'd only be looking at rows 1:11. Les Stout wrote: Hi Tom, i have tried it again and cell G11827 has the string "Total due to supplier" in it, so i want it to delete this row, but it does not ? Les Stout *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find part of string & delete row
Thant did the trick, thanks Dave
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete part of a text string | Excel Worksheet Functions | |||
delete a - if it is the last part of a string | Excel Discussion (Misc queries) | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
Use a formula to delete part of a text string? | Excel Worksheet Functions | |||
How do I delete part of a text string in every cell it appears in | Excel Discussion (Misc queries) |