Hi Rick,

I am trying to learn from this example.
If you wouldn't mind, could you clarify how sizing the array to the mod
returned here works for this. I can't understand it yet, and getting lazy I
suppose.

ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)


(Also, this routine fails when row count is too high. Over about 2700
lines or so) Not sure why, but it goes out of range)

Thanks,

Ariel

"Rick Rothstein (MVP - VB)" wrote in
message ...
It appears you want to concatenate every 50 rows of data in Column A into
single String values (using a semi-colon as a delimiter) and place them
back into Column A after first clearing all the data in Column A (that is,
Column A's original data structure will be lost). If that is correct, this
subroutine should do what you want (although I'd suggest testing it out on
sample data first to be sure the end result is actually what you want)...

Sub GroupBy50sColumnA()
Dim X As Long, Z As Long
Dim LastRow As Long
Dim CombinedRows As String
Dim CombinedValues() As String
Const GroupCount As Long = 50
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
For X = 1 To LastRow Step GroupCount
CombinedRows = Cells(X, "A").Value
For Z = 1 To GroupCount - 1
If Z + X LastRow Then Exit For
CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value
Next
CombinedValues(1 + X \ GroupCount) = CombinedRows
Next
Range("A:A").ClearContents
For X = 1 To UBound(CombinedValues)
Cells(X, "A").Value = CombinedValues(X)
Next
End Sub

Rick


"AndrewJ" wrote in message
...
I'm looking to the experts on this one as I'm a very basic VBA user
and not sure where to start. I'm looking for some help in buildig a
macro that could do the following. Take a column of data such as
below
and then Consolidate the data 50 cells at a time with the data
seperated by a semi-colon(no spaces) into 1 cell. For Instance:

A
1 11111
2 11112
3 11113
4 .....


2223 12223


to


A
1 11111;11112;11113...etc
2 11151;11152;11153...etc


I'm sure there's a way to do it. I'll be honest. I have no idea where
to start. If it's not clear what I'm trying to do, please let me
know.
Any help at all will be appreciated.


Thanks!