Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
Would anybody have a procedure to find a specific word such as "Composite" in
a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
Do a DataFilterAutofilter and filter on "Composite", then delete the
visible rows. -- HTH RP (remove nothere from the email address if mailing direct) "Abilio" wrote in message ... Would anybody have a procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
It would have to be a macro procedure.
"Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
You can give this a try...
Sub Test() Call DeleteRows("Composite") End Sub Sub DeleteRows(ByVal DeleteString As String) Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim rngFirst As Range Set wksToSearch = Sheets("Sheet1") Set rngToSearch = wksToSearch.Cells Set rngFound = rngToSearch.Find(What:=DeleteString, LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "Nothing was found to delete.", vbInformation, "Nothing Found" Else Set rngFirst = rngFound Set rngFoundAll = rngFound.EntireRow Do Set rngFoundAll = Union(rngFound.EntireRow, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFirst.Address = rngFound.Address rngFoundAll.Delete End If End Sub -- HTH... Jim Thomlinson "Abilio" wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
You could record a macro when you do it manually. It'll be very close to what
you want. Or... Option Explicit Sub testme02() Dim FoundCell As Range Do With Worksheets("Sheet1").Range("g1").EntireColumn Set FoundCell = .Cells.Find(What:="composite", _ After:=.Cells(1), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If Loop End Sub Abilio wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
Thank you very much Jim!
"Jim Thomlinson" wrote: You can give this a try... Sub Test() Call DeleteRows("Composite") End Sub Sub DeleteRows(ByVal DeleteString As String) Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim rngFirst As Range Set wksToSearch = Sheets("Sheet1") Set rngToSearch = wksToSearch.Cells Set rngFound = rngToSearch.Find(What:=DeleteString, LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "Nothing was found to delete.", vbInformation, "Nothing Found" Else Set rngFirst = rngFound Set rngFoundAll = rngFound.EntireRow Do Set rngFoundAll = Union(rngFound.EntireRow, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFirst.Address = rngFound.Address rngFoundAll.Delete End If End Sub -- HTH... Jim Thomlinson "Abilio" wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
Jim, How could I adapt that to search for textbox 4's contents, and then
delete? "Jim Thomlinson" wrote: You can give this a try... Sub Test() Call DeleteRows("Composite") End Sub Sub DeleteRows(ByVal DeleteString As String) Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim rngFirst As Range Set wksToSearch = Sheets("Sheet1") Set rngToSearch = wksToSearch.Cells Set rngFound = rngToSearch.Find(What:=DeleteString, LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "Nothing was found to delete.", vbInformation, "Nothing Found" Else Set rngFirst = rngFound Set rngFoundAll = rngFound.EntireRow Do Set rngFoundAll = Union(rngFound.EntireRow, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFirst.Address = rngFound.Address rngFoundAll.Delete End If End Sub -- HTH... Jim Thomlinson "Abilio" wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
Sub Test()
Call DeleteRows(Userform1.Textbox4.Text) End Sub If you want the whole row deleted change rngFoundAll.Delete to rngFoundAll.EntireRow.Delete -- Regards, Tom Ogilvy "jeramie" wrote in message ... Jim, How could I adapt that to search for textbox 4's contents, and then delete? "Jim Thomlinson" wrote: You can give this a try... Sub Test() Call DeleteRows("Composite") End Sub Sub DeleteRows(ByVal DeleteString As String) Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim rngFirst As Range Set wksToSearch = Sheets("Sheet1") Set rngToSearch = wksToSearch.Cells Set rngFound = rngToSearch.Find(What:=DeleteString, LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "Nothing was found to delete.", vbInformation, "Nothing Found" Else Set rngFirst = rngFound Set rngFoundAll = rngFound.EntireRow Do Set rngFoundAll = Union(rngFound.EntireRow, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFirst.Address = rngFound.Address rngFoundAll.Delete End If End Sub -- HTH... Jim Thomlinson "Abilio" wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding A string
nevermind, I got it.
"jeramie" wrote: Jim, How could I adapt that to search for textbox 4's contents, and then delete? "Jim Thomlinson" wrote: You can give this a try... Sub Test() Call DeleteRows("Composite") End Sub Sub DeleteRows(ByVal DeleteString As String) Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim rngFirst As Range Set wksToSearch = Sheets("Sheet1") Set rngToSearch = wksToSearch.Cells Set rngFound = rngToSearch.Find(What:=DeleteString, LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "Nothing was found to delete.", vbInformation, "Nothing Found" Else Set rngFirst = rngFound Set rngFoundAll = rngFound.EntireRow Do Set rngFoundAll = Union(rngFound.EntireRow, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFirst.Address = rngFound.Address rngFoundAll.Delete End If End Sub -- HTH... Jim Thomlinson "Abilio" wrote: It would have to be a macro procedure. "Abilio" wrote: Would anybody have a macro procedure to find a specific word such as "Composite" in a column and delete the respective rows where the string were found. I appreciate the help! Thank you, Abilio Andries |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding a name in a string | Excel Worksheet Functions | |||
finding what numbers are in a string (Day 2) | Excel Worksheet Functions | |||
finding what numbers are in a string | Excel Worksheet Functions | |||
Finding text in a string | Excel Programming | |||
Finding a string | Excel Programming |