Combining Multiple Columns into one column without any blank cells

mdesroc

New Member
Joined
Dec 8, 2012
Messages
18
I need to be able to combine 3 columns with ranges that may change into one column without any blank cells.

For example, I have columns B, C and D filled with data, but the number of cells in each of those columns will change based on user choices other places in the spreadsheet.

Is it possible to combine the data from B, C, D into Column A automatically and if so how would I go about doing that?

Thank you for any help or advice you can offer.
 
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:
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top