Usage:
Generated UNION SELECT sql statement:
select 'adf' as A1, 'dfa' as B1, 'ad' as C1 from dual union select 'sdddd' as A1, 'af' as B1, 'd' as C1 from dual union select 'adf' as A1, 'dfa' as B1, '' as C1 from dual union select 'fa' as A1, '' as B1, 'af' as C1 from dual union select 'd' as A1, 'adf' as B1, '' as C1 from dual union select '' as A1, '' as B1, 'adf' as C1 from dual union select 'df' as A1, 'a' as B1, 'f' as C1 from dual
Limitations:
* Headers are assumed to exist - row 1 is used for column aliasing
* The function expects a single contiguous range of data. Example: you can't pass in something like =UnionSelectRange(A2:D2, F2)
* If the selected range is one row, each row gets a 'union'. You'll have to manually remove the union from the first row
* If the selected range is multiple rows, Excel will put double quotes around the copied union select. Simply remove them to use the SQL.
* Oracle limitations - maximum number of selected columns is 1000, maximum column alias length is 30 characters
Source:
Public Function UnionSelectRange(dataRange As Range) As String Dim st As String Dim x As Long, y As Long For x = 1 To dataRange.Rows.Count If x > 1 Then st = st & Chr(13) & Chr(10) & "union " End If 'Add a union if doing a row at a time If dataRange.Rows.Count = 1 Then st = st & "union " End If st = st & "select " For y = 1 To dataRange.Columns.Count If y > 1 Then st = st & ", " End If st = st & "'" & dataRange.Cells(x, y).Value & "' as " & Sheets(dataRange.Parent.Name).Cells(1, dataRange.Cells(x, y).Column).Value Next y st = st & " from dual" Next x UnionSelectRange = st End Function