Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 15th 14, 07:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 33
Default Excel 2013 Merge Data

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

  #2   Report Post  
Old April 15th 14, 08:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2011
Posts: 3,514
Default Excel 2013 Merge Data

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


  #3   Report Post  
Old April 16th 14, 04:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2011
Posts: 3,514
Default Excel 2013 Merge Data

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ListObjects - VBA code that worked in previous versions of Excel doesnot work in Excel 2013 hdf Excel Programming 2 November 17th 14 01:34 AM
Macro from older version into Office 2013 on Windows 8 [email protected] Excel Programming 3 March 5th 14 07:08 PM
Help creating new functions for Excel 2013 Jschock New Users to Excel 4 August 20th 13 01:25 AM
Does Publisher 2013 have autoflow? Martin [_2_] Excel Discussion (Misc queries) 0 March 12th 13 04:22 PM
Update calendar for 2013 - 2020 with events from the database sheet ganzzu Excel Programming 0 February 15th 13 01:22 PM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017