Combining Multiple Columns into one column without any blank cells

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
here is one that runs sql query that can be modified to your needs

Code:
Sub doSQL()


    Dim strCon As String
    Dim oneSQL As String
    
    ' refer to 'microsoft activex data objects library'
    Dim cn As Object
    Dim rs As Object


    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source='" & ThisWorkbook.FullName & "';" & _
              "Extended Properties='Excel 12.0;HDR=No;IMEX=1';"    ' HDR=No  means no headers (field names)


    cn.Open strCon     ' open connection
    
'-------------------------------------------------------------------------------

    ' F1, F2, F3 are the default fieldnames when no headers are included with data    


    oneSQL = "SELECT F3 FROM [Sheet1$B:D] where F3 not like '' union all " & _
             "SELECT F1 FROM [Sheet1$B:D] where F1 not like '' union all " & _
             "SELECT F2 FROM [Sheet1$B:D] where F2 not like ''; "
    
    rs.Open oneSQL, cn      ' get recordset


    Sheets("Sheet1").Range("A:A").ClearContents

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs     ' copy recordset to worksheet
    
'-------------------------------------------------------------------------------
    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing


End Sub
 
Last edited:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Lizlpn

New Member
Joined
Sep 20, 2016
Messages
1
Hi VoG, I know that this post is quite long time ago, but could you pls help me as I have the similar concern with 2 additional conditions:
1. The first row of column A need to be empty; and
2. The source columns will be updated frequently base on the new key in data, thereby the result column (assuming columns A) need to be updated accordingly.

Thank you so muchhh
 

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,468
Members
407,601
Latest member
soccer4ward

This Week's Hot Topics

Top