How to select a subset of a table with Excel VBA in the manner of SELECT -> FROM -> WHERE

Question

Hi guys,

Apologies is this is the classic newbie question, but I haven't been able to find a concrete answer.

I'm entirely new to Excel VBA and to programming in general. What I'm trying to do is select a subset of data from a large excel sheet that's roughly in this format:

    Name          Data One     Data Two    Data N
    ----------------------------------------------------
    Person One       x            x          x
    Person One       x            x          x
    Person One       x            x          x
    Person Two       x            x          x
    Person Two       x            x          x
    Person Three     x            x          x

I will have a new sheet to deal withe every few days, but I won't know how many people will be listed on it or how many entries there will be for each person.

My end goal (for now...) is to take all the data for Person One and copy that to a new sheet (in the same workbook) called Person One, copy all the data for Person Two to a new sheet called Person Two, and so on.

If my rudimentary understanding of SQL is correct I would be able to use a command in the form of SELECT data FROM table WHERE Person = Person One.

However, I hacen't been able to find a simple way to do this in Excel. I can see there are ways that I could do it by using a loop to cycle through the row and note the point where the data change from 'Person One' to 'Person Two' and then define those ranges.

I also see that I can use the AutoFilter function to select the data that I want - I could filter the first column for unique records on a second sheet, then cycle through the names of each person in the AutoFilter setting.

It has also occurred to me I could use a loop to run through the entries in the first column and insert a blank row under each, so the table select function would select each chunk of data for each person.

Is there a better way to accomplish my end though? I would have thought this scenario was pretty common, but either it isn't or I'm searching on the wrong things.

Cheers!

Stephen

Answer 1

You can use ADO:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = ActiveWorkbook.FullName

''Note HDR=No, then F1,F2 etc is used for column names
''If HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

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


cn.Open strCon

'Case sensitive
strSQL = "SELECT [Name],[Data One],[Data Two],[Data N] FROM [Sheet1$] " _
       & "WHERE [Name]='Person 1'"


rs.Open strSQL, cn, 3, 3
Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

Note that you could select distinct [name] and cycle through the resulting recordset to get a sheet for each person.