How to find ranges in a sheet

[Guest]

Hi,

There are many ranges in an excel sheet. But some of these ranges being with
letter TG_ and some with letters PG_ . rest of the letters in the name are
not knows. Kindly assist me in finding out which all ranges are there which
start with letter TG_ and after finding that range , how to scan through the
range to read the values present in the cell.

Your assistance is really appreciated.

Thanks

 

[Tom Ogilvy]

you can do it all at once

Dim rng as Range, rng1 as Range
Dim nm as Name
for each nm in thisworkbook.Names
set rng = Nothing
on Error Resume Next
set rng = nm.RefersToRange
On Error goto 0
if lcase(left(nm.name,2)) = "tg" then
if rng.parent.Name = Activesheet.Name then
msgbox nm.Name & ", " & rng.Address & ": " & rng.Text
if rng1 is nothing then
set rng1 = rng
else
set rng1 = union(rng,rng1)
end if
end if
end if
Next
if not rng1 is nothing then
rng1.Select
end if