Hello,

In what has turned out to be a massive project, I am reaching the final
stages.
However I need a macro to automate a tedius task, and was hoping that
some of you out there might be able to provide one or at least part of
one. I have tried to be as articulate and specific as possible in
explaining what i need so here goes...

For a given row y ; copy the contents of every 4th column to a
concatenated string, and paste this string into the column labeled GOD
(i'm using excel 2007 - because i ran out of columns in excel 2003!) of
row y. The contents of each column that forms part of the string should
be seperated by six spaces, i.e. " ".

I need the macro to run this for every row in the worksheet.
(there are about 2000 rows, and one row has 5125 columns!

I look forward to your thoughts,

Gary.

Gary,
Personally, if you need 5000+ columns, your structure needs to be rewored,
but...
Need sometidying up, but it Friday night and off to the pub. I 'll see what
I can do over the weekend:

Private Sub CommandButton1_Click()
Dim RowIndex As Long
Dim ColumnIndex As Long
Dim CelVal As String
Dim TempStr As String
Dim DestColumn As Long

Const COLUMN_STEP As Long = 4
Const SPACER As Long = 6

With ActiveCell.CurrentRegion
DestColumn = .Columns.Count + COLUMN_STEP
For RowIndex = 0 To .Rows.Count - 1
With .Range("A1").Offset(RowIndex, 0)
TempStr = ""
ColumnIndex = COLUMN_STEP
CelVal = .Offset(0, ColumnIndex).Value
Do Until CelVal = ""
TempStr = TempStr & CelVal & Space(SPACER)
ColumnIndex = ColumnIndex + COLUMN_STEP
CelVal = .Offset(0, ColumnIndex).Value
Loop
Debug.Print TempStr
If Len(TempStr) 0 Then ActiveCell.CurrentRegion.Cells(RowIndex
+ 1, DestColumn).Value = Left(TempStr, Len(TempStr) - SPACER)
End With
Next
End With
End Sub

Try it on a 20 x 20 selection of you data first to make sure it's giving the
correct result.
Exact size doesn't matter, as it works on .CurrentRegion, so selct any cell
in the data and click go.

NickHK



groups.com...
Hello,

In what has turned out to be a massive project, I am reaching the final
stages.
However I need a macro to automate a tedius task, and was hoping that
some of you out there might be able to provide one or at least part of
one. I have tried to be as articulate and specific as possible in
explaining what i need so here goes...

For a given row y ; copy the contents of every 4th column to a
concatenated string, and paste this string into the column labeled GOD
(i'm using excel 2007 - because i ran out of columns in excel 2003!) of
row y. The contents of each column that forms part of the string should
be seperated by six spaces, i.e. " ".

I need the macro to run this for every row in the worksheet.
(there are about 2000 rows, and one row has 5125 columns!

I look forward to your thoughts,

Gary.