Hi,
Ive been handed a spreadsheet which Im trying to replicate in VBA, but Im trying to work out the best way to do it. I have a Row of Numbers , the rows span from column C -> Column FZ, 180 columns
From column 1 starting from C, I have various numbers, some have blanks inbetween, others not. and what I would like to do is concatenate the entire range. - See image
This was the formula that did it, but that has to be a much better way of doing it, especially in VBA
=CONCATENATE(IF(LEN(C3)=0,"",C3&", "),IF(LEN(D3)=0,"",D3&", "),IF(LEN(E3)=0,"",E3&", "),IF(LEN(F3)=0,"",F3&", "),IF(LEN(G3)=0,"",G3&", "),IF(LEN(H3)=0,"",H3&", "),IF(LEN(I3)=0,"",I3&", "),IF(LEN(J3)=0,"",J3&", "),IF(LEN(K3)=0,"",K3&", "),IF(LEN(L3)=0,"",L3&", "),IF(LEN(M3)=0,"",M3&", "),IF(LEN(N3)=0,"",N3&", "),IF(LEN(O3)=0,"",O3&", "),IF(LEN(P3)=0,"",P3&", "),IF(LEN(Q3)=0,"",Q3&", "),IF(LEN(R3)=0,"",R3&", "),IF(LEN(S3)=0,"",S3&", "),IF(LEN(T3)=0,"",T3&", "),IF(LEN(U3)=0,"",U3&", "),IF(LEN(V3)=0,"",V3&", "),IF(LEN(W3)=0,"",W3&", "),IF(LEN(X3)=0,"",X3&", "),IF(LEN(Y3)=0,"",Y3&", "),IF(LEN(Z3)=0,"",Z3&", "),IF(LEN(AA3)=0,"",AA3&", "),,IF(LEN(AB3)=0,"",AB3&", "),IF(LEN(AC3)=0,"",AC3&", "),IF(LEN(AD3)=0,"",AD3&", "),IF(LEN(AE3)=0,"",AE3&", "),IF(LEN(AF3)=0,"",AF3&", "),IF(LEN(AG3)=0,"",AG3&", "),IF(LEN(AH3)=0,"",AH3&", "),IF(LEN(AI3)=0,"",AI3&", "),IF(LEN(AJ3)=0,"",AJ3&", "),IF(LEN(AK3)=0,"",AK3&", "),IF(LEN(AL3)=0,"",AL3&", "),IF(LEN(AM3)=0,"",AM3&", "),IF(LEN(AN3)=0,"",AN3&", "),IF(LEN(AO3)=0,"",AO3&", "),IF(LEN(AP3)=0,"",AP3&", "),IF(LEN(AQ3)=0,"",AQ3&", "),IF(LEN(AR3)=0,"",AR3&", "),IF(LEN(AS3)=0,"",AS3&", "),IF(LEN(AT3)=0,"",AT3&", "),IF(LEN(AU3)=0,"",AU3&", "),IF(LEN(AV3)=0,"",AV3&", "),IF(LEN(AW3)=0,"",AW3&", "),IF(LEN(AX3)=0,"",AX3&", "),IF(LEN(AY3)=0,"",AY3&", "),IF(LEN(AZ3)=0,"",AZ3&", "),IF(LEN(BA3)=0,"",BA3&", "),IF(LEN(BB3)=0,"",BB3&", "),IF(LEN(BC3)=0,"",BC3&", "),IF(LEN(BD3)=0,"",BD3&", "),IF(LEN(BE3)=0,"",BE3&", "),IF(LEN(BF3)=0,"",BF3&", "),IF(LEN(BG3)=0,"",BG3&", "),IF(LEN(BH3)=0,"",BH3&", "),IF(LEN(BI3)=0,"",BI3&", "),,IF(LEN(BJ3)=0,"",BJ3&", "),IF(LEN(BK3)=0,"",BK3&", "),IF(LEN(BL3)=0,"",BL3&", "),IF(LEN(BM3)=0,"",BM3&", "),IF(LEN(BN3)=0,"",BN3&", "),IF(LEN(BO3)=0,"",BO3&", "),IF(LEN(BP3)=0,"",BP3&", "),IF(LEN(BQ3)=0,"",BQ3&", "),IF(LEN(BR3)=0,"",BR3&", "),IF(LEN(BS3)=0,"",BS3&", "),IF(LEN(BT3)=0,"",BT3&", "),IF(LEN(BU3)=0,"",BU3&", "),IF(LEN(BV3)=0,"",BV3&", "),IF(LEN(BW3)=0,"",BW3&", "),IF(LEN(BX3)=0,"",BX3&", "),IF(LEN(BY3)=0,"",BY3&", "),IF(LEN(BZ3)=0,"",BZ3&", "),IF(LEN(CA3)=0,"",CA3&", "),IF(LEN(CB3)=0,"",CB3&", "),IF(LEN(CC3)=0,"",CC3&", "),IF(LEN(CD3)=0,"",CD3&", "),IF(LEN(CE3)=0,"",CE3&", "),IF(LEN(CF3)=0,"",CF3&", "),IF(LEN(CG3)=0,"",CG3&", "),IF(LEN(CH3)=0,"",CH3&", "),IF(LEN(CI3)=0,"",CI3&", "),IF(LEN(CJ3)=0,"",CJ3&", "),IF(LEN(CK3)=0,"",CK3&", "),IF(LEN(CL3)=0,"",CL3&", "),IF(LEN(CM3)=0,"",CM3&", "),IF(LEN(CN3)=0,"",CN3&", "),IF(LEN(CO3)=0,"",CO3&", "),IF(LEN(CP3)=0,"",CP3&", "),IF(LEN(CQ3)=0,"",CQ3&", "),IF(LEN(CR3)=0,"",CR3&", "),IF(LEN(CS3)=0,"",CS3&", "),IF(LEN(CT3)=0,"",CT3&", "),IF(LEN(CU3)=0,"",CU3&", "),IF(LEN(CV3)=0,"",CV3&", "),IF(LEN(CW3)=0,"",CW3&", "),IF(LEN(CX3)=0,"",CX3&", "))
Is there any way of cutting this Wall of Formula down and making it work in VBA for speed. (this is only on one cell, it then has to go down to the bottom of the row based on how many rows there are on A.
thanks
David
Ive been handed a spreadsheet which Im trying to replicate in VBA, but Im trying to work out the best way to do it. I have a Row of Numbers , the rows span from column C -> Column FZ, 180 columns
From column 1 starting from C, I have various numbers, some have blanks inbetween, others not. and what I would like to do is concatenate the entire range. - See image
This was the formula that did it, but that has to be a much better way of doing it, especially in VBA
=CONCATENATE(IF(LEN(C3)=0,"",C3&", "),IF(LEN(D3)=0,"",D3&", "),IF(LEN(E3)=0,"",E3&", "),IF(LEN(F3)=0,"",F3&", "),IF(LEN(G3)=0,"",G3&", "),IF(LEN(H3)=0,"",H3&", "),IF(LEN(I3)=0,"",I3&", "),IF(LEN(J3)=0,"",J3&", "),IF(LEN(K3)=0,"",K3&", "),IF(LEN(L3)=0,"",L3&", "),IF(LEN(M3)=0,"",M3&", "),IF(LEN(N3)=0,"",N3&", "),IF(LEN(O3)=0,"",O3&", "),IF(LEN(P3)=0,"",P3&", "),IF(LEN(Q3)=0,"",Q3&", "),IF(LEN(R3)=0,"",R3&", "),IF(LEN(S3)=0,"",S3&", "),IF(LEN(T3)=0,"",T3&", "),IF(LEN(U3)=0,"",U3&", "),IF(LEN(V3)=0,"",V3&", "),IF(LEN(W3)=0,"",W3&", "),IF(LEN(X3)=0,"",X3&", "),IF(LEN(Y3)=0,"",Y3&", "),IF(LEN(Z3)=0,"",Z3&", "),IF(LEN(AA3)=0,"",AA3&", "),,IF(LEN(AB3)=0,"",AB3&", "),IF(LEN(AC3)=0,"",AC3&", "),IF(LEN(AD3)=0,"",AD3&", "),IF(LEN(AE3)=0,"",AE3&", "),IF(LEN(AF3)=0,"",AF3&", "),IF(LEN(AG3)=0,"",AG3&", "),IF(LEN(AH3)=0,"",AH3&", "),IF(LEN(AI3)=0,"",AI3&", "),IF(LEN(AJ3)=0,"",AJ3&", "),IF(LEN(AK3)=0,"",AK3&", "),IF(LEN(AL3)=0,"",AL3&", "),IF(LEN(AM3)=0,"",AM3&", "),IF(LEN(AN3)=0,"",AN3&", "),IF(LEN(AO3)=0,"",AO3&", "),IF(LEN(AP3)=0,"",AP3&", "),IF(LEN(AQ3)=0,"",AQ3&", "),IF(LEN(AR3)=0,"",AR3&", "),IF(LEN(AS3)=0,"",AS3&", "),IF(LEN(AT3)=0,"",AT3&", "),IF(LEN(AU3)=0,"",AU3&", "),IF(LEN(AV3)=0,"",AV3&", "),IF(LEN(AW3)=0,"",AW3&", "),IF(LEN(AX3)=0,"",AX3&", "),IF(LEN(AY3)=0,"",AY3&", "),IF(LEN(AZ3)=0,"",AZ3&", "),IF(LEN(BA3)=0,"",BA3&", "),IF(LEN(BB3)=0,"",BB3&", "),IF(LEN(BC3)=0,"",BC3&", "),IF(LEN(BD3)=0,"",BD3&", "),IF(LEN(BE3)=0,"",BE3&", "),IF(LEN(BF3)=0,"",BF3&", "),IF(LEN(BG3)=0,"",BG3&", "),IF(LEN(BH3)=0,"",BH3&", "),IF(LEN(BI3)=0,"",BI3&", "),,IF(LEN(BJ3)=0,"",BJ3&", "),IF(LEN(BK3)=0,"",BK3&", "),IF(LEN(BL3)=0,"",BL3&", "),IF(LEN(BM3)=0,"",BM3&", "),IF(LEN(BN3)=0,"",BN3&", "),IF(LEN(BO3)=0,"",BO3&", "),IF(LEN(BP3)=0,"",BP3&", "),IF(LEN(BQ3)=0,"",BQ3&", "),IF(LEN(BR3)=0,"",BR3&", "),IF(LEN(BS3)=0,"",BS3&", "),IF(LEN(BT3)=0,"",BT3&", "),IF(LEN(BU3)=0,"",BU3&", "),IF(LEN(BV3)=0,"",BV3&", "),IF(LEN(BW3)=0,"",BW3&", "),IF(LEN(BX3)=0,"",BX3&", "),IF(LEN(BY3)=0,"",BY3&", "),IF(LEN(BZ3)=0,"",BZ3&", "),IF(LEN(CA3)=0,"",CA3&", "),IF(LEN(CB3)=0,"",CB3&", "),IF(LEN(CC3)=0,"",CC3&", "),IF(LEN(CD3)=0,"",CD3&", "),IF(LEN(CE3)=0,"",CE3&", "),IF(LEN(CF3)=0,"",CF3&", "),IF(LEN(CG3)=0,"",CG3&", "),IF(LEN(CH3)=0,"",CH3&", "),IF(LEN(CI3)=0,"",CI3&", "),IF(LEN(CJ3)=0,"",CJ3&", "),IF(LEN(CK3)=0,"",CK3&", "),IF(LEN(CL3)=0,"",CL3&", "),IF(LEN(CM3)=0,"",CM3&", "),IF(LEN(CN3)=0,"",CN3&", "),IF(LEN(CO3)=0,"",CO3&", "),IF(LEN(CP3)=0,"",CP3&", "),IF(LEN(CQ3)=0,"",CQ3&", "),IF(LEN(CR3)=0,"",CR3&", "),IF(LEN(CS3)=0,"",CS3&", "),IF(LEN(CT3)=0,"",CT3&", "),IF(LEN(CU3)=0,"",CU3&", "),IF(LEN(CV3)=0,"",CV3&", "),IF(LEN(CW3)=0,"",CW3&", "),IF(LEN(CX3)=0,"",CX3&", "))
Is there any way of cutting this Wall of Formula down and making it work in VBA for speed. (this is only on one cell, it then has to go down to the bottom of the row based on how many rows there are on A.
thanks
David
