Concatenate multiple rows then move to next column

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
I'm having trouble with this. I've tried searching but can't find anything that helps.

This is the current code I have:
Code:
With Sheets("Crew 4")
        ' determine the end-of-data col
        i = .Cells(4, .Columns.Count).End(xlToLeft).Column
        If i < 2 Then
            MsgBox "No data on CREW sheet."
            GoTo Xit
        End If
        ' insert a temporary column
        .Columns("A:A").Insert Shift:=xlToRight
        
        With .Range("A4:A" & i)
            ' seed formula in A4-down than concatenate the Job+Lot+(Phase+Code)
            .FormulaR1C1 = "=R4C4&"":""&R5C4&"":""&R7C4&R8C4"

This is my Data layout:


Excel 2010
BDEFG
4Job NumberDRRC15013DRRC15013DRRC15013DRRC15013
5Lot #31313131
6Model / Elev.1839183918391839
7PhaseSCLHLHLH
8Code10103080
9Budget18.00631.00203.0031.00
Crew 4


What I am trying to do, starting in Col D, Concat. Rows 4, 5,7, & 8 into the newly inserted Col A4 then move down to A5 and do the same thing with Col E, etc. etc. to the end of the Variable i which was set to Col 17 prior to the insertion of row A.

This is the results I get currently in Col A:


Excel 2010
A
4DRRC15013:31:SC10
5DRRC15013:31:SC10
6DRRC15013:31:SC10
7DRRC15013:31:SC10
Crew 4


It gives the same value as col D, as you can see.
I'm only showing four rows but Col A has only 14 entries all the same, not 17 entries.

I need help so A5 reads Col E, A6 reads Col F, etc.

(Col C hidden sensitive info)

Thanks in advance.
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Can I ask why are you inserting a "Temporary" column in at column A? If you're just using column A to concatenate the data, the macro could do the concatenations without inserting a temporary column. What are you doing with the results?
 
Upvote 0
Thanks for the reply

I am doing this because later on in my procedure I will be using what is in Col A do a search & match of what is in A to bring in a value off of another Data sheet for row 9. This is why I have the colon in between the Concat. cells so during my search & match it will bring in the right amount based on the values between the colons

I currently have a procedure (written by somebody else) that does something similar but in a Vertical range of just one column. Which later on in that code, does this search & match as mentioned. I think I understand "most" of it while stepping though it. That current procedure works just fine. What I'm trying to do is dissect it and make it work with a different set up.

I am trying to adjust (rewrite) this to work off of my setup of Rows and Columns to populate col A.

From there I am hoping that I can continue to "adjust" this current procedure to do what I want. I may have other issue as I progress further down on the procedure

If I can get Col A to populate then I can proceed. I'm not looking for anybody to rewrite my current procedure. I'm trying to do this on my own, but of course I know that I may need a little help along the way.

I am doing this in self taught VBA mode.

By the way, Col A is only temporary it will be deleted later on In the code

Thanks


.
 
Last edited:
Upvote 0
Good enough reason; you're in self-taught VBA mode.

Decision:
Your formula has to change column references as it's populated down rows.
A4 =D4&":"&D5&":"&D7&D8
A5 =E4&":"&E5&":"&E7&E8
A6 =F4&":"&F5&":"&F7&F8

You can 1.) change the formula so it will offset columns as it's copied down rows, or 2.) use VBA to populate a unique formula in each row where VBA changes the column references for each cell.
 
Upvote 0
Thanks for the reply.

Maybe my statement of self-taught VBA mode wasn't the most wisest description. I am trying to do what I can by dissecting current macros and do as many searches as I can do before posting for help.

Your suggestion #2 is what I'm trying to do, through VBA. As my attempt above shows, I can't get it to work. I think I don't understand how to make cell or col reference absolute / non-absolute in VBA.

As I have been working on this today, it seems to me that it may be better to copy to a new sheet versus inserting a column ??? Not sure.

And to enhance on your question: What are you doing with the results?

I will be comparing what is in rows 4:8 to bring in a value to Row 9. If the users have a INCORRECT entry in Rows 4:8 there will not be an entry in Row 9. This coding is further down in the procedure.

For now, I just need to populate Rows 4:8 starting at "D" to Last column on current sheet. Maybe way over on Col CA instead of the new Col A, or Add Another Sheet for population, (which of course I can delete after the macro runs. Or delete want is put in col CA.)
 
Last edited:
Upvote 0
Code:
    [color=darkblue]Dim[/color] LastCol [color=darkblue]As[/color] [color=darkblue]Long[/color], col [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] Sheets("Crew 4")
        
        [color=green]' determine the end-of-data col[/color]
        LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
        
        [color=darkblue]If[/color] LastCol < 4 [color=darkblue]Then[/color]
            MsgBox "No data on CREW sheet."
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
        [color=darkblue]For[/color] col = 4 [color=darkblue]To[/color] LastCol
            .Cells(col, LastCol + 1).FormulaR1C1 = "=R4C" & col & "&"":""&R5C" & col & "&"":""&R7C" & col & "&R8C" & col
        [color=darkblue]Next[/color] col
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
 
Upvote 0
Thank you sir

This will work. Have a great day.

You're welcome.

Just so you're aware that for this part...
Code:
        [COLOR=darkblue]For[/COLOR] col = 4 [COLOR=darkblue]To[/COLOR] LastCol
            .Cells(col, LastCol + 1).FormulaR1C1 = "=R4C" & col & "&"":""&R5C" & col & "&"":""&R7C" & col & "&R8C" & col
        [COLOR=darkblue]Next[/COLOR] col

...VBA could have just as easily concatenated the worksheet values rather than concatenate the formulae to then concatenate the values.
 
Upvote 0
...VBA could have just as easily concatenated the worksheet values rather than concatenate the formulae to then concatenate the values
.

If you don't mind showing that, I'd appreciate it.

I have a lot of room for learning.
 
Upvote 0
Code:
        [color=darkblue]For[/color] col = 4 [color=darkblue]To[/color] LastCol
            .Cells(col, LastCol + 1).Value = .Cells(4, col) & ":" & .Cells(5, col) & ":" & .Cells(7, col) & .Cells(8, col)
        [color=darkblue]Next[/color] col
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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