execute sql query on excel columns using vba

Question

I'm doing excel data verification and would like to view any discrepancies within each column. And I think executing

"select distinct column1 from table_name"

is efficient. Is there a way to do that in vba with/without connecting to any database? Any advice would be appreciated.

neophyte · Accepted Answer · 2014-05-16 16:18:55Z

1

This code should do the SELECT DISTINCT for one column:

ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True

The code will paste the Distinct values from activesheet column A to column B. Hope this helps

answered May 16, 2014 at 16:18

neophyte

1,8363 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot. It helps. But I don't want to change the raw file anyway :)

If you need something more specific let me know.. I answered according to question ;)

Okay. I want the queried results as a recordset or something and handle the results in vba. So is creating an object connection the only option?

you could loop for every cell in the column.. if the count of cell value is 1 in the column add it to an array