Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to copy every nth column of a row to the first cell of the row?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to copy every nth column of a row to the first cell of the row?
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. wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Column and copy to cell Column D | Excel Worksheet Functions | |||
Copy last cell with data in column E on one sheet to cell on anoth | Excel Worksheet Functions | |||
How do I copy every 8th cell in a column | Excel Discussion (Misc queries) | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming |