Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"like" string
I need to delete a row that has certain values in a cell
in Column A. For example if the word "Average" appears in cell a, I need to delete the whole row. I can't figure out how to write the code to find partial string match. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"like" string
Maybe you could apply a filter to column A and look for Contains: Average.
Then delete those visible cells. (record a macro when you do it once for the code). or Option Explicit Sub testme01() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim delRng As Range With Worksheets("sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 If InStr(1, .Cells(iRow, 1).Value, "average", _ vbTextCompare) 0 Then If delRng Is Nothing Then Set delRng = .Cells(iRow, 1) Else Set delRng = Union(delRng, .Cells(iRow, 1)) End If End If Next iRow If delRng Is Nothing Then 'do nothing Else delRng.EntireRow.Delete End If End With End Sub MDC wrote: I need to delete a row that has certain values in a cell in Column A. For example if the word "Average" appears in cell a, I need to delete the whole row. I can't figure out how to write the code to find partial string match. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"like" string
This macro will delete a row if the 3 letters 'Ave' are the 1st 3 letters
in the cell Easily adapted. Sub delete_if_Ave() For Each c In Selection If Left(c, 3) = "Ave" Then c.EntireRow.Delete Next End Sub "MDC" wrote in message ... I need to delete a row that has certain values in a cell in Column A. For example if the word "Average" appears in cell a, I need to delete the whole row. I can't figure out how to write the code to find partial string match. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"like" string
That will tend to skip some rows if two or more consecutive rows contain the
condition. Sub delete_if_Ave() Dim lrow as long, i as long, c as range lrow = selection(selection.rows.count).Row For i = lrow to selection.row step -1 set c = cells(i,activecell.column) If Left(c, 3) = "Ave" Then c.EntireRow.Delete Next End Sub so looping from the highest row to the lowest row avoids this problem. -- Regards, Tom Ogilvy "JayL." wrote in message news:c9lob.52118$ao4.141844@attbi_s51... This macro will delete a row if the 3 letters 'Ave' are the 1st 3 letters in the cell Easily adapted. Sub delete_if_Ave() For Each c In Selection If Left(c, 3) = "Ave" Then c.EntireRow.Delete Next End Sub "MDC" wrote in message ... I need to delete a row that has certain values in a cell in Column A. For example if the word "Average" appears in cell a, I need to delete the whole row. I can't figure out how to write the code to find partial string match. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"like" string
Good Point! Thanks Tom
"Tom Ogilvy" wrote in message ... That will tend to skip some rows if two or more consecutive rows contain the condition. Sub delete_if_Ave() Dim lrow as long, i as long, c as range lrow = selection(selection.rows.count).Row For i = lrow to selection.row step -1 set c = cells(i,activecell.column) If Left(c, 3) = "Ave" Then c.EntireRow.Delete Next End Sub so looping from the highest row to the lowest row avoids this problem. -- Regards, Tom Ogilvy "JayL." wrote in message news:c9lob.52118$ao4.141844@attbi_s51... This macro will delete a row if the 3 letters 'Ave' are the 1st 3 letters in the cell Easily adapted. Sub delete_if_Ave() For Each c In Selection If Left(c, 3) = "Ave" Then c.EntireRow.Delete Next End Sub "MDC" wrote in message ... I need to delete a row that has certain values in a cell in Column A. For example if the word "Average" appears in cell a, I need to delete the whole row. I can't figure out how to write the code to find partial string match. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions |