VBA - Selecting Based on Criteria

Group,
Lets say I have two columns, in column A I have state names like Illinois, Indiana and Iowa. Lets also say in column B I have numbers. How can I select a range of cells containing numbers for Illinois. The states will be continuously used. In other words, all the Illinois cells will follow each other, then right after the final Illinois cell an Indiana range of cells will be continuous and so forth. On any range selection, I'm only selecting one state at a time. Your thoughts how I can overcome this problem.


Thanks again for all your assistance........


Tony

try autofilter and copy flltered cells somewhere else


--
remove $$$ from email addresss to send email

===================
ajocius <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> Lets say I have two columns, in column A I have state names like
> Illinois, Indiana and Iowa. Lets also say in column B I have numbers.
> How can I select a range of cells containing numbers for Illinois. The
> states will be continuously used. In other words, all the Illinois
> cells will follow each other, then right after the final Illinois cell
> an Indiana range of cells will be continuous and so forth. On any
> range selection, I'm only selecting one state at a time. Your thoughts
> how I can overcome this problem.
>
>
> Thanks again for all your assistance........
>
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=393624
>

Tony,

Here is an example

Dim iLastRow As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns(1).AutoFilter
Columns(1).AutoFilter Field:=1, Criteria1:="Illinois"
Set rng = Range("A1:A" & iLastRow).SpecialCells(xlCellTypeVisible)
Columns(1).AutoFilter
rng.Select


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ajocius" <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> Lets say I have two columns, in column A I have state names like
> Illinois, Indiana and Iowa. Lets also say in column B I have numbers.
> How can I select a range of cells containing numbers for Illinois. The
> states will be continuously used. In other words, all the Illinois
> cells will follow each other, then right after the final Illinois cell
> an Indiana range of cells will be continuous and so forth. On any
> range selection, I'm only selecting one state at a time. Your thoughts
> how I can overcome this problem.
>
>
> Thanks again for all your assistance........
>
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=393624
>