Need idea(s) on how to minimize code for handling groups of cells

[frateg8r]

I have a worksheet with unique numbers in each cell in column A; the number of cells with data could be anywhere from 2 to 999. The data would thus look like this:

XXXX0000000

XXXX1111111

XXXX2222222

I will then need to output those values, in groups of not more than 5, to a row elsewhere, such as:

range("D1").Value = A1 & " " & A2 & " " & A3 & " " & A4 & " " & A5

etc....

Rather than hardcode each possible combination of 5 cells into its own line (as in repeating that line over and over, then manually adjusting the cell addresses for each line...sheesh, that would be tedious), I'm trying to think of how I can accomplish that in the minimal amount of code. I'm sure there is a way to use variables and other if/then or for each/next scenarios to accomplish this, and I will figure it out eventually. But, on the chance that someone might have a ready idea at hand, I would appreciate any insights/direction. I've got a tremendous amount of code I can refer back to, but I just don't recall having done anything quite like this before.

 

[Herakles]

I have a worksheet with unique numbers in each cell in column A; the number of cells with data could be anywhere from 2 to 999. The data would thus look like this:

XXXX0000000

XXXX1111111

XXXX2222222

I will then need to output those values, in groups of not more than 5, to a row elsewhere, such as:

range("D1").Value = A1 & " " & A2 & " " & A3 & " " & A4 & " " & A5

etc....

Rather than hardcode each possible combination of 5 cells into its own line (as in repeating that line over and over, then manually adjusting the cell addresses for each line...sheesh, that would be tedious), I'm trying to think of how I can accomplish that in the minimal amount of code. I'm sure there is a way to use variables and other if/then or for each/next scenarios to accomplish this, and I will figure it out eventually. But, on the chance that someone might have a ready idea at hand, I would appreciate any insights/direction. I've got a tremendous amount of code I can refer back to, but I just don't recall having done anything quite like this before.

Does this work?

Assumes that the worksheet is named 'Data' but you can change this.

Public Sub subGroupRows()
Dim Ws As Worksheet
    
    Set Ws = Worksheets("Data")
    
    With Ws.Range("D2:D" & Int(Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row / 5) + 2)
        .Formula2 = "=TEXTJOIN(" & """" & "  " & """" & ",TRUE,OFFSET($A$1,((ROW()-2)*5)+1,0,5,1))"
        .Value = .Value
    End With
            
End Sub

 

[frateg8r]

.Formula2 = "=TEXTJOIN(" & """" & " " & """" & ",TRUE,OFFSET($A$1,((ROW()-2)*5)+1,0,5,1))"

I can get the formula into the target cell, but nothing displays.

• Can you clarify for me what the (ROW()-2*5) is referring to? I understand the position of the formula is the number of rows, but I'm not clear on if it is referring to the number of rows in the origin data, or the destination range. I also get that ROW() is pulling the row# of the instant cell - yet, I'm not clear on what cell that is intended to be (again, origin or destination). Knowing these things might help me figure this out (this is my first encounter with TEXTJOIN).

 

[frateg8r]

I can get the formula into the target cell, but nothing displays.

• Can you clarify for me what the (ROW()-2*5) is referring to? I understand the position of the formula is the number of rows, but I'm not clear on if it is referring to the number of rows in the origin data, or the destination range. I also get that ROW() is pulling the row# of the instant cell - yet, I'm not clear on what cell that is intended to be (again, origin or destination). Knowing these things might help me figure this out (this is my first encounter with TEXTJOIN).

Never mind! I just needed some time to think of a different approach to figuring this out, and I did. This works perfectly - thank you! I learned something as well, so it's a big win for me.

 

[Peter_SSs]

Perhaps you need vba for your purposes but just in case, this can be done with a single formula in a single cell.
Change the 5 in the WRAPROWS part of the formula for a different number of rows to group.

frateg8r.xlsm
	A	B	C	D
1
2	X0			X0 X1 X2 X3 X4
3	X1			X5 X6 X7 X8 X9
4	X2			X10 X11
5	X3
6	X4
7	X5
8	X6
9	X7
10	X8
11	X9
12	X10
13	X11
14
Data

Cell Formulas
Range	Formula
D2:D4	D2	=BYROW(WRAPROWS(A2:A13,5,""),LAMBDA(rw,TEXTJOIN(" ",,rw)))
Dynamic array formulas.