Excel - Concatenating Rows in List 1

[PeterMoran]

Hi,

I have a problem where I have a single column list of text approx 300 rows long (B1:B300). A number of these rows are in fact parts 1, 2 and 3 of what I want to be one row. I would like someone to show me how a macro can be built to take the active cell, copy the row below it to the end of the cell data after adding a space, delete the copied row, and then repeat the exercise for the next row. Thus ending with the 2nd and 3rd cells of data concatenated to the end of the first.

I attempted to do this using the macro recoder thinking I would have something to work with, but the result was useless as it just indicated the end result of the concatenated strings at each stage without any commands to show me how it should be coded.

Once this is achieved I hope to flag the first row of the cells to be concatenated with an "*" in col A, so the whole function can be done in the one go.

Thanks in anticipation.

Peter Moran

 

[Molby]

Peter,

Here's some code to be getting on with, it will take whatever value is in the activecell if it contains a star and add the two values below it. You can record yourself deleting rows to see how to do that.

Sub test()
if instr(1,activecell.value,"*") then
a$ = ActiveCell.Value & " " & ActiveCell.Offset(1, 0).Value & " " & ActiveCell.Offset(2, 0).Value
End if
End Sub

 

[Zathras]

It sounds like you have a variable number of lines. This routine doesn't care how many lines as long as the first one is identified with an asterisk in column "A"

Sub CombineRows()
Dim nRow As Long
  nRow = 1
  While Cells(nRow + 1, 2) <> ""
    If Cells(nRow, 1) = "*" Then
      If Cells(nRow + 1, 1) <> "*" Then
        Cells(nRow, 2) = Cells(nRow, 2) + " " + Cells(nRow + 1, 2)
        Rows(nRow + 1).Delete
      Else
        nRow = nRow + 1
      End If
    End If
  Wend
End Sub

***CAUTION*** Any data you may have in columns "C" thru "IV" will be lost on all but the first line for each kit.

 

[boggg1]

Molby and Zathras have got you going on the code side but does your second paragraph imply you have recorded a macro but cannot find the code that has been recorded ? On my version (Excel 2000) you will find this under Tools/Macro/Visual Basic Editor then double click Modules then the particular Module name that you used to store your code (my system default here is Module1).

Maybe I'm misunderstanding what you are saying but you cannot do any serious Visual Basic coding if you can't find the code !

Boggg1

 

[jcrater]

Dim intStartRow As Integer
Dim intCurrRow As Integer
Dim intLastRow As Integer
Dim iRow As Integer

intStartRow = 1
intCurrRow = intStartRow

Cells(intCurrRow, 2).Select
Selection.End(xlDown).Select
intLastRow = Selection.Row

For iRow = intLastRow To intStartRow Step -3
Cells(iRow - 2, 2).Select
ActiveCell.Value = ActiveCell.Value & " " & ActiveCell.Offset(1, 0) & " " & ActiveCell.Offset(2, 0)
Range(Cells(iRow - 1, 2), Cells(iRow, 2)).Select
Selection.EntireRow.Delete
Next iRow

 

[RooSXL]

jcrater! It works marvelously...

I have a question though. What does it do the

[\code] For iRow=intLastRow to StartRow Step -3 [code][\code]

My logic tells me that when it finishes up with the first set of contanetation it is only backing 2 rows, therefore including the very last that it is already formatted.  Just simple curiosity to learn something new.

Thanks,

 

[PeterMoran]

Hi,

Thanks guys for all your input while I was sound asleep in Melbourne Australia.

Certainly have something to work with now!!

Will try and work thru the suggestions today.

Many Thanks,

Peter Moran
Two (or many) heads are always better than one!