How to return a range of cells in VBA without using a loop?

Question

let's say I have a excel spread sheet like below:

col1   col2
------------
dog1   dog
dog2   dog
dog3   dog
dog4   dog
cat1   cat
cat2   cat
cat3   cat

I want to return a range of cells (dog1,dog2,dog3,dog4) or (cat1,cat2,cat3) based on either "dog" or "cat"

I know I can do a loop to check one by one, but is there any other method in VBA so I can "filter" the result in one shot?

maybe the Range.Find(XXX) can help, but I only see examples for just one cell not a range of cells.

Please advice

Regards

Answer 1

Here are some notes on using a recordset to return the range.

Sub GetRange()
Dim cn As Object
Dim rs As Object
Dim strcn, strFile, strPos1, strPos2

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    strFile = ActiveWorkbook.FullName

    strcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & strFile & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

    cn.Open strcn

    rs.Open "SELECT * FROM [Sheet1$]", cn, 3 'adOpenStatic'

    rs.Find "Col2='cat'"
    strPos1 = rs.AbsolutePosition + 1
    rs.MoveLast
    If Trim(rs!Col2 & "") <> "cat" Then
        rs.Find "Col2='cat'", , -1 'adSearchBackward'
        strPos2 = rs.AbsolutePosition + 1
    Else
        strPos2 = rs.AbsolutePosition + 1
    End If
    Range("A" & strPos1, "B" & strPos2).Select
End Sub