A Christmas treat for my OZgrid friends. :gift:
Wraping the VBA Find/FindNext methods into a function you can use in your code to return found range objects!
This function is awesome, and the uses are many...
Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As Variant, _ Optional LookAt As Variant, _ Optional MatchCase As Boolean) As Range Dim c As Range If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole If IsMissing(MatchCase) Then MatchCase = False With Search_Range Set c = .Find( _ What:=Find_Item, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=MatchCase, _ SearchFormat:=False) If Not c Is Nothing Then Set Find_Range = c firstAddress = c.Address Do Set Find_Range = Union(Find_Range, c) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Function
This should work in 2002 and later. You may have to tweak it a bit to work with earlier versions. For instance, I don't think SearchFormat is an option for the FIND method in 2000 and earlier. You can wipe that line if needed.
Here's just a few of the potential uses for this function...
Select all cells in a range that contain 22 as part of the value:
Clear the range if the cell contains exactly 999, but if it's a formula leave it be:
Delete all rows that contain "X" in column A:
Quickly scan the whole sheet if you like!
How often have you seen people asking, "How do I find all rows that match a criteria and paste the results to a new sheet?" Now you can do it in a single line of code. For those who can appreciate it, this last one is pretty amazing!
Copy all the rows that have the value 1000 in column D and paste to Sheet2:
Find_Range(1000, Columns("D"), xlFormulas, xlWhole).EntireRow.Copy Range("Sheet2!A1")