PDA

View Full Version : Selecting values from different rows in a table and merge them in order in one row



From
09-05-2014, 12:52 AM
Hi,

I want to use a formula, or set of formulas, for selecting values from a data table were the number of values differ between rows (see attachment). I do not want to cut and paste or use any commands as I need to make this a executable function.

For instance, 9 rows and 21 cols (as in attachment), in the first row i have 21 values, in the 2nd only 4 and in the 3rd I have 5 etc. I want to "merge" these values in order in a single row, i.e. from B2 to V2 (in this case) and then continue with B3 to E3, and B4 to F4 etc, all in a single new row.

So in the table I have a total of 103 values in 9 rows, i want to sort them in a single row, i.e. from e.g. B15 to CZ15 (I think it will be CZ).

12231

Thanks!
Fredrik

snb
09-05-2014, 01:39 AM
Your 'empty' cells contain spaces !


Sub M_snb()
sn = Cells(1).CurrentRegion.Offset(, 1)
ReDim sp(UBound(sn) * UBound(sn, 2))

For Each it In sn
If Trim(it) <> "" Then
sp(j) = it
j = j + 1
End If
Next

Cells(20, 1).Resize(, UBound(sp) + 1) = sp
End Sub