Skip to content

Instantly share code, notes, and snippets.

@DBremen
Created September 4, 2015 11:40
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DBremen/d840be45ba5c82e6d874 to your computer and use it in GitHub Desktop.
Save DBremen/d840be45ba5c82e6d874 to your computer and use it in GitHub Desktop.
VBA Excel to cross-join multiple ranges without duplicates
Sub CrossJoinRanges()
Dim cn As ADODB.Connection
Dim sql As String
Dim outputSheet As Worksheet
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 XML;HDR=Yes"""
.Open
End With
sql = "SELECT DISTINCT * FROM [Sheet1$A1:A2], [Sheet1$B1:B3], [Sheet1$C1:C3]"
rs.Open sql, cn
Set outputSheet = Sheets.Add
outputSheet.Name = "CrossJoined"
outputSheet.Range("A1").CopyFromRecordset rs
rs.Close
cn.Close
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment