VBA - Selecting Based on Criteria

[ajocius]

Group,
Lets say I have two columns, in column A I have state names lik
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. Th
states will be continuously used. In other words, all the Illinoi
cells will follow each other, then right after the final Illinois cel
an Indiana range of cells will be continuous and so forth. On an
range selection, I'm only selecting one state at a time. Your thought
how I can overcome this problem.


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


Ton

 

[R.VENKATARAMAN]

try autofilter and copy flltered cells somewhere else

 

[Bob Phillips]

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)