Searching a column (Repost)
Hi Tom,
Thanks for the help, the code worked great. One question
if I may. how can I clear the variable rng after a loop?
My macro that this code is a part of opens a file, runs
some code (including this code), opens the nest file and
runs the same code, It appears that I need to clear the
rng variable after each time it runs. I'm getting an error
about the Union failing. Any advice would be greatly
appreciated.
Thanks again,
Todd
-----Original Message-----
Sub TEST2()
Dim rng as Range, rng1 as Range
Dim SearchRange as Range
Dim cell as Range
Set SearchRange = _
Range("F2:F" & _
Range("F65536").End(xlUp).Row)
For Each Cell In SearchRange
If Len(Cell.Value) 0 And _
Cell.Value < "Size" And _
Cell.Value < "Grand Total" And _
Cell.Font.Bold = True Then
set rng1 = _
Range(Cell.Offset(0, 0).Address & ":" & _
Cell.Offset(0, -1).Address)
if rng is nothing then
set rng = rng1
else
set rng = union(rng, rng1)
end if
End If
Next Cell
if not rng is nothing then
rng.select
End if
End Sub
--
Regards,
Tom Ogilvy
"ToddG" wrote in
message
...
The following code searches column F and selects certain
cells. I need it to select ALL cells on the sheet that
meet the "search" criteria, not just the LAST cells that
meet the "search" criteria. Any suggestions would be
appreciated.
Sub TEST2()
Set SearchRange = Range("F2:F" & Range("F65536").End
(xlUp).Row)
For Each Cell In SearchRange
If Len(Cell.Value) 0 And _
Cell.Value < "Size" And _
Cell.Value < "Grand Total" And _
Cell.Font.Bold = True Then
Range(Cell.Offset(0, 0).Address & ":" & Cell.Offset(0, -
1).Address).Select
End If
Next Cell
End Sub
.
|