Consolidate Data

[AndrewJ]

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!

 

[GerryGerry]

Try the following:

Public Function consolidate(rngConsolidate As range) As String
Dim cell As range
consolidate = ""
For Each cell In rngConsolidate
consolidate = consolidate & cell.Value & ";"
Next
End Function

 

[Dave Peterson]

Check your other post, too.

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!

 

[GerryGerry]

Assuming the data is always in column A and the first blank cell is where
you want it to stop, the following code should do it

Public Sub consolidate()
Dim intRowMarker As Integer, intCurrentRow As Integer, strConsolidation
As String, intCount As Integer
intRowMarker = 1
intCurrentRow = 1
strConsolidation = ""
Do
For intCount = 1 To 50
If (range("A" & intCurrentRow).Value = "") Then Exit For
strConsolidation = strConsolidation & range("A" &
intCurrentRow).Value & ";"
intCurrentRow = intCurrentRow + 1
Next intCount
range("A" & intRowMarker).Value = strConsolidation
strConsolidation = ""
intRowMarker = intRowMarker + 1
Loop While (range("A" & intCurrentRow).Value <> "")
End Sub