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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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
 

doharr

Board Regular
Joined
Jul 28, 2009
Messages
77
Sorry, Norie this did not work. I had a runtime error.

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

Thanks
 

doharr

Board Regular
Joined
Jul 28, 2009
Messages
77

ADVERTISEMENT

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:

doharr

Board Regular
Joined
Jul 28, 2009
Messages
77
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

I'm not sure if I can help further but what is the exact error message and which version of Excel do you have?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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?
 

doharr

Board Regular
Joined
Jul 28, 2009
Messages
77
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

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
Top