Page 5 of 5 FirstFirst ... 345
Results 41 to 42 of 42

Thread: Combining Multiple Columns into one column without any blank cells
Thanks Thanks: 0 Likes Likes: 0

  1. #41
    Board Regular
    Join Date
    Nov 2013
    Posts
    524
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Multiple Columns into one column without any blank cells

    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 by jsotola; Dec 12th, 2013 at 04:06 AM.

  2. #42
    New Member
    Join Date
    Sep 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Multiple Columns into one column without any blank cells

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •