Excel 2013 Merge Data

Discussion in 'Excel Programming' started by jfcby, Apr 15, 2014.

  1. jfcby

    jfcby Guest

    Hi,

    I have a worksheet that I trying to merge data from multiple cells into one cell.

    Lets say that worksheet one columns A1-E1 are labeled as Column 1 through Column 5. Each cell in column 1 is labeled row1-column1, row2-column1, etc.

    My macro will put the column header and row1-column1 etc in worksheet 2 row 1 column on. The problem I'm having is when the cell text is wraped the each value is on a separate line like...

    row1-column1
    row2-column1
    row3-column1

    I need the text to side by side like row1-column1, row2-column1, row3-column1.

    How can I get my macro to merge the the data so that it will be displayed like row1-column1, row2-column1, row3-column1 when the cell is merged?

    Macro:

    'Copy sheet1 data to sheet2
    is2 = 2
    For is1 = 4 To s1rowNum 'rows
    Worksheets(wksName2).Range("A" & is2) = Worksheets(wksName1).Range("A" & is1)
    Worksheets(wksName2).Range("B" & is2) = Worksheets(wksName1).Range("B" & is1)
    Worksheets(wksName2).Range("C" & is2) = Worksheets(wksName1).Range("C" & is1)

    For is3 = 1 To 3 'columns
    If Worksheets(wksName1).Cells(is1, is3) <> "" Then
    cTrim1 = Trim(Worksheets(wksName1).Cells(3, is3).Text)
    cTrim2 = Worksheets(wksName1).Cells(is1, is3).Text
    'vCell = Trim(vCell & Worksheets(wksName1).Cells(3, is3).Text & "-|-" & " (" & Worksheets(wksName1).Cells(is1, is3).Text & ") ")
    vCell = vCell & cTrim1 & cTrim2
    End If
    Next

    Thanks for your help,
    Frankie
     
    jfcby, Apr 15, 2014
    #1
    1. Advertisements

  2. jfcby

    GS Guest

    In a standard module...


    Option Explicit

    Sub XferColsToRows()
    Dim vData, n&
    vData = Sheets("Sheet1").UsedRange
    For n = LBound(vData) To UBound(vData, 2)
    With Sheets("Sheet2")
    .Cells(n, 1).Value = _
    Join(Application.Transpose(Application.Index(vData, 0, n)),
    ",")
    End With 'Sheets("Sheet2")
    Next 'n
    End Sub

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 15, 2014
    #2
    1. Advertisements

  3. jfcby

    GS Guest

    Alternatively...

    Sub XferColsToRows()
    Dim vData, n&
    vData = Sheets("Sheet1").UsedRange
    For n = LBound(vData) To UBound(vData, 2)
    Sheets("Sheet2").Cells(n, 1).Value = _
    Join(Application.Transpose(Application.Index(vData, 0, n)), ",")
    Next 'n
    End Sub

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 16, 2014
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. chcoach
    Replies:
    0
    Views:
    218
    chcoach
    Mar 7, 2006
  2. Replies:
    4
    Views:
    113
    Dave Peterson
    Nov 4, 2006
  3. Mathias Fritsch

    Import Data / Edit Data / Export Data in Excel

    Mathias Fritsch, Jul 19, 2007, in forum: Excel Programming
    Replies:
    0
    Views:
    189
    Mathias Fritsch
    Jul 19, 2007
  4. Replies:
    3
    Views:
    229
  5. Worksmart
    Replies:
    0
    Views:
    237
    Worksmart
    May 18, 2008
  6. ganzzu
    Replies:
    0
    Views:
    105
    ganzzu
    Feb 15, 2013
  7. hdf
    Replies:
    0
    Views:
    213
  8. Replies:
    3
    Views:
    187
Loading...