How do I get this VBA Code to loop to the next row down

doharr

Board Regular
Joined
Jul 28, 2009
Messages
77
I found this code from Al Chara, I modified it some. Now I just need to get it to loop down to the next row

Thanks
====================================================

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("B1:AC1")
For Each cel In rng
x = x & cel.Value
Next
.Range("a1").Value = x
End With
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Possibly

Code:
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range, LR As Long, i As Long
With ActiveSheet
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Set rng = .Range("B" & i & ":AC" & i)
        x = ""
        For Each cel In rng
            x = x & cel.Value
        Next cel
        .Range("A" & i).Value = x
    Next i
End With
End Sub
 
Upvote 0
Try this.
Code:
Sub ConcatenateAll()
Dim rng As Range
Dim arrVals
 
Set rng = Range("B1")    
 
    While rng.Value <> ""

        arrVals = rng.Resize(, 28)
 
        arrVals = Application.Transpose(arrVals)
 
        arrVals = Application.Transpose(arrVals)
 
        rng.Offset(, -1) = Join(arrVals, "")
 
        Set rng = rng.Offset(1)
 
    Wend
 
End Sub
 
Upvote 0
Sorry, Norie this did not work. I had a runtime error.

When debugged, error highlighted:
arrVals = Application.Transpose(arrVals)

Thanks
 
Upvote 0
VoG this did work on the first 8172 rows. Then it failed when it runs into memory issues. A memory issue message pops up on the screen. My machine is 2gb memory, 4+gb disk space, XP professional. This failure is simular to Excel's standard Concatenate formula. When I make this standard function , =CONCATENATE(B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1,AA1,AB1,AC1).or this one=concatenate(B1&C1&D1......&AC1), it too fails at this point.

Once it runs into the memory issue the program is no longer able to proceed unless it is restarted.

Thanks for any help.
 
Last edited:
Upvote 0
VoG this did work on the first 8172 rows. Then it failed when it runs into memory issues. A memory issue message pops up on the screen. My machine is 2gb memory, 4+gb disk space, XP professional. This failure is simular to Excel's standard Concatenate formula. When I make this standard function , =CONCATENATE(B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1,AA1,AB1,AC1).or this one=concatenate(B1&C1&D1......&AC1), it too fails at this point.

Once it runs into the memory issue the program is no longer able to proceed unless it is restarted.

Thanks for any help.
 
Upvote 0
I'm not sure if I can help further but what is the exact error message and which version of Excel do you have?
 
Upvote 0
Well it worked for me, though I was only using data that I improvised based on your description.:)

What are you trying to do anyway?

Why are you concatenating all these columns?
 
Upvote 0
I will send you a Test file latter today. I will have to make it up, as I'm not allowed to send the orginial file out. I will leave the link on this page. It links to my server. Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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