Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a column (Repost)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a column (Repost)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost of hide a column | Excel Discussion (Misc queries) | |||
Searching One Column Only | Excel Discussion (Misc queries) | |||
repost from general area ref values with column to left HELP | Excel Worksheet Functions | |||
RePost: Summing column in 5D Array | Excel Programming | |||
Searching a Column | Excel Programming |