# Cell range with dynamic rows

#### vanilz

##### New Member
Hi Excel expert,

I have a table that row can be added depend on the needs. I would like to combine the rows into 1 cell.
A1 KY012345
A2 KY023456
A3 KY034567
A4 …
A5 …

I am using =CONCATENATE(A1:A3)
The result should be KY012345, KY023456, KY034567, …, … depends on the rows.
In this case, the number of rows cannot be determined. Hence, cannot use fix cell range.

Would you able to help with the formula to get A3 change according to the row added?

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### jasonb75

##### Well-known Member
If you format as a proper table then the range will be dynamic.
Book1
ABC
1ListA1 KY012345A2 KY023456A3 KY034567
2A1 KY012345
3A2 KY023456
4A3 KY034567
Sheet5
Cell Formulas
RangeFormula
C1C1=CONCAT(Table1[List])

#### vanilz

##### New Member
Sorry, I'm quite new with excel formula. Hope to get the correct formula for below.
How to define it as table as mentioned above?

For this example, if user add row 7 with KY0999832, the result would not have the 4th account no.

#### CA_Punit

##### Well-known Member
just press Ctrl+T and press ok
The ranges will ber converted into table and when you enter a new data in A7 will be be automatically incorporated in the result
and please dont select the cell indiviually in the concat function use like stated below

Book1
BCDEFGH
2List
3A1 KY012345A1 KY012345A2 KY023456A3 KY034567punit
4A2 KY023456
5A3 KY034567
6punit
7
8
9
Sheet1
Cell Formulas
RangeFormula
F3F3=CONCAT(Table1
• )

#### vanilz

##### New Member

Thank you.

When I tried to converted to table, the merged column split into 2 columns automatically. How to merge it?

I have macro to add row, however, when add new row, it is not incorporated in the result.

#### CA_Punit

##### Well-known Member

Even if it is merged all the data will be transferred to the first column and then you can concat it. Leave the extra column generated

#### jasonb75

##### Well-known Member

the merged column split into 2 columns automatically.
It wasn't merged in your screen capture.

Regardless of that, don't merge cells. When it comes to formulas merged cells are the root of all things evil.

For the formula, use =TEXTJOIN(", ",1,Table1
• ) instead, then you don't have to keep adding the commas.

#### vanilz

##### New Member
Thank you. I understood on above.

I adding rows with macro, but it did not incorporate with the table. How to set the new rows incorporate with the table?

#### jasonb75

##### Well-known Member

First merged cells, now macro. Is there any other missing information that you are likely to add later?

Replies
0
Views
75
Replies
6
Views
180
Replies
3
Views
154
Replies
5
Views
146
Replies
10
Views
157

1,129,352
Messages
5,635,785
Members
416,882
Latest member
ericvrealty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back