Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

CONCATENATE ROWS – EXCEL

Locked

We receive RFQ’s via email in excel 5.0 format. Each wksheet (42 separate wksheets per wkbook) has approximately 2500 rows of info organized as
COL A = PART NUMBER (one cell)
COL B = DESCRIPTION (from one to 7 cells in the column)

I need to make a VB macro which would allow me to concatenate all the cells in COL B into one cell, while referencing the individual part number in COL A.

The following Macro works some…but not OK
Sub ConcatColumns ()
Do While ActiveCell <> “” ‘Loops until the active cell is blank.
‘The “&” must have a space on both sides or it will be treated
‘as a variable type of long integer.
ActiveCell.Offsett(0, 1).FormulaR1C1 = _
ActiveCell.Offsett(0, -1) & ” ” & ActiveCell.Offsett(0, 0)
ActiveCell.Offsett(1, 0).Select
Loop
End Sub

What happens is that I cannot
a)
get all the cells in COL B (for Description) relating to any ONE PART Number (in COL A) to be specifically concatenated into COL C,in one cell.
b)
ex

Topic

CONCATENATE ROWS – EXCEL

In reply to CONCATENATE ROWS – EXCEL

Function loogy(wks As Excel.Worksheet)
Dim cl As Excel.Range, row As Excel.Range, sList
‘insert a new column
wks.Columns(2).Insert
For Each row In wks.UsedRange.Rows
sList = vbNullString
‘collect all the values after the new column into a list
For iCell = 3 To row.Cells.Count
Set cl = row.Cells(iCell)
If “” = cl.Value Then Exit For
sList = sList + CStr(cl.Value) + Chr(59)
Next iCell
‘assign the list to the new column
row.Cells(2) = sList
Next row
End Function

CONCATENATE ROWS – EXCEL

In reply to CONCATENATE ROWS – EXCEL

The question was auto-closed by TechRepublic

CONCATENATE ROWS – EXCEL

In reply to CONCATENATE ROWS – EXCEL

My friend sometime has to do such a hard and boring work like what you are experiencing. She complaint to me and all the time I had to create macros for her that’s why I really sympathy for you. I have tested this piece of code and it ran well.
I’mafraid my answer exceed 1000 characters limit and you cannot load it. If so, feel free to contact me at Ngo_The_Doung@mail.ryoyu.co.jp
Good luck,
Duong beo

Sub Macro1()
‘ Macro1 Macro
‘ Keyboard Shortcut: Ctrl+Shift+T
Dim MyRow, MyResultRow As Integer
Dim MyPartColumn, MyDescColumn, MyResultColumn As String
Dim MyTempString As String
Dim MyResult As String
Dim i, j As Integer

MyRow = 1
MyResultRow = MyRow
MyPartColumn = “A”
MyDescColumn = “B”
MyResultColumn = “C”
MyPartValue= Worksheets(“Sheet1”).Range(MyPartColumn + LTrim(Str(MyRow))).Value

While MyPartValue <> “”
MyTempString = Worksheets(“Sheet1″).Range(MyPartColumn + LTrim(Str(MyRow))).Value
MyResult = ” ”
While Worksheets(“Sheet1”).Range(MyPartColumn + LTrim(Str(MyRo

CONCATENATE ROWS – EXCEL

In reply to CONCATENATE ROWS – EXCEL

The question was auto-closed by TechRepublic

CONCATENATE ROWS – EXCEL

In reply to CONCATENATE ROWS – EXCEL

This question was auto closed due to inactivity

[Author]

Replies