how to copy every nth column of a row to the first cell of the row?

[garyusenet]

How do you copy every nth column of a row to the first cell of the row?


The values in the columns are string values and I'd like to place a
space between every value as it's inserted into the cell.

There are thousands of columns (using excel 2007) and each row is using
a different number of columns, so the macro will need to figure out
when the last column of data is in the row and stop at that point.

 

[Mike]

Here's one quick way to do it - create a User defined function:

Public Function EveryNth(Spacing As Range, source As Range) As String
Dim bytCounter As Byte
Dim strTemp As String
Set source = Intersect(source, Application.Caller.EntireRow)
If Not (source Is Nothing) Then
Set source = Range(source.Cells(1, 1), source.Cells(1,
1).End(xlToRight))
For bytCounter = 1 To Int(source.Columns.Count / Spacing.Value)
strTemp = strTemp & " " & source.Cells(1, bytCounter *
Spacing.Value)
Next bytCounter
End If
EveryNth = Trim(strTemp)
End Function

where spacing is a cell with the value for 'Nth', and source would be the
range containing your source strings (it can be either the first column of
your source row, the entire source row, or even the entire source range)

hth.

Mike.