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, _
- If Not c Is Nothing Then
- Set Find_Range = c
- firstAddress = c.Address
- 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")