# Number of a rows in a range reference using a formula?

#### ubundom

##### New Member
My formula is:

Code:
``=CELL("address",Sheet1!B2:E4)``
and Excel correctly shows the address of the top left cell in the range:

Code:
``[Test.xls]Sheet1!\$A\$1``
I would like to use a formula that will tell me that the number of rows in this range is 3 and the numbers of columns is 4, but, without using a macro or having to define a range name. Any ideas?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

1

Last edited:

#### ubundom

##### New Member

As a starter for one ... what is your idea jimbo?

#### VoG

##### Legend
Try

=ROWS(Sheet1!\$B\$2:\$E\$4)

=COLUMNS(Sheet1!\$B\$2:\$E\$4)

#### ubundom

##### New Member
Re: Number of rows in a range reference using a formula?

Try

=ROWS(Sheet1!\$B\$2:\$E\$4)

=COLUMNS(Sheet1!\$B\$2:\$E\$4)

Yep! that works, but I would prefer to have formula that "extracts" the dimensions of the range in the formula that is stored in the cell as described. I.e.: get the height and width in rows and columns.

Um, it might not be possible in a formula; it is trivial in VB but I must avoid macros, for all the usual reasons.

#### VoG

##### Legend
Re: Number of rows in a range reference using a formula?

I would prefer to have formula that "extracts" the dimensions of the range in the formula that is stored in the cell as described.

Sorry but I have absolutely no idea what you mean by that

#### ubundom

##### New Member
Re: Number of rows in a range reference using a formula?

Sorry but I have absolutely no idea what you mean by that

Oh sorry ... more explanation: I am interested in processing a bunch of ranges, these are:

Sheet1!A1:E4
Sheet1!B5:G6
Sheet2!B7:F10

I have stored these in a table in a table (in Sheet3 say) thus:

I have done it like this so that:
a) each range is only stored once (inside the formula) and
b) so that I can access the workbook name (in an XSL processor) thusly:

[Test.xls]Sheet1!A1:E4
[Test.xls]Sheet1!B5:G6
[Test.xls]Sheet2!B7:F10

I can use ROW(INDIRECT(range)) and COLUMN(INDIRECT(range)) to give me the start row and column of a range that is found in the bunch of ranges at the top of this reply. Now I need: LASTROW(INDIRECT(range)) and LASTCOLUMN(INDIRECT(range)).

This will avoid having to have (the obvious but inefficient) further column containing the range (a second instance) and therefore be prone to "user error" and other mistypings.

Thanks so much for your ongoing interest!

#### VoG

##### Legend
With your example of B2:E4 does this help?

<b>Sheet14</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:129px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Row</td><td >Column</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >No of rows</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Start</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >End</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=ROWS(Sheet1!\$B\$2:\$E\$4)</td></tr><tr><td >C2</td><td >=COLUMNS(Sheet1!\$B\$2:\$E\$4)</td></tr><tr><td >B3</td><td >=ROW(Sheet1!B2:E4)</td></tr><tr><td >C3</td><td >=COLUMN(Sheet1!B2:E4)</td></tr><tr><td >B4</td><td >=B2+B3-1</td></tr><tr><td >C4</td><td >=C2+C3-1</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

#### ubundom

##### New Member
With your example of B2:E4 does this help?
Sheet14
<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 129px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 20px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td>Row</td><td>Column</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>No of rows</td><td style="text-align: right;">3</td><td style="text-align: right;">4</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Start</td><td style="text-align: right;">2</td><td style="text-align: right;">2</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>End</td><td style="text-align: right;">4</td><td style="text-align: right;">5</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0); width: 196px; height: 156px;"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt; width: 179px; height: 116px;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td>=ROWS(Sheet1!\$B\$2:\$E\$4)</td></tr><tr><td>C2</td><td>=COLUMNS(Sheet1!\$B\$2:\$E\$4)</td></tr><tr><td>B3</td><td>=ROW(Sheet1!B2:E4)</td></tr><tr><td>C3</td><td>=COLUMN(Sheet1!B2:E4)</td></tr><tr><td>B4</td><td>=B2+B3-1</td></tr><tr><td>C4</td><td>=C2+C3-1</td></tr></tbody></table></td></tr></tbody></table>
That would be all OK if I didn't think that someone might inadvertently mis-key the B2:E4 and so I was hoping for a formula that would be like this so that there is only one instance of the range names stored in the CELL formula in column A:
<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; width: 481px; height: 97px;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 129px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td style="vertical-align: top;">D
</td><td style="vertical-align: top;">E
</td></tr><tr style="height: 20px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Address
</td><td>RowStart</td><td>RowEnd</td><td style="vertical-align: top;">ColStart
</td><td style="vertical-align: top;">ColEnd
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>[Test.xls]Sheet1!\$B\$2</td><td style="text-align: right;">2</td><td style="text-align: right;">4</td><td style="vertical-align: top;" align="right">2
</td><td style="vertical-align: top;" align="right">5
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>[Test.xls]Sheet1!\$A\$10</td><td style="text-align: right;">10</td><td style="text-align: right;">16</td><td style="vertical-align: top;" align="right">1
</td><td style="vertical-align: top;" align="right">6
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>[Test.xls]Sheet2!\$C\$2</td><td style="text-align: right;">2</td><td style="text-align: right;">4</td><td style="vertical-align: top;" align="right">3
</td><td style="vertical-align: top;" align="right">5
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0); width: 196px; height: 156px;"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" width="226" border="1" cellpadding="2" cellspacing="0" height="169"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A2</td><td>=CELL("address",Sheet1!B2:E4)</td></tr><tr><td>B2</td><td>=ROW(INDIRECT(A2))</td></tr><tr><td style="vertical-align: top;">C2
</td><td style="vertical-align: top;">=COLUMN(INDIRECT(A2))
</td><td valign="top">=ROW(INDIRECT .... etc
</td></tr></tbody></table></td></tr></tbody></table>

Last edited:

Replies
1
Views
192
Replies
4
Views
217
Replies
5
Views
204
Replies
1
Views
165
Replies
3
Views
346

1,190,913
Messages
5,983,539
Members
439,848
Latest member
timmyo

### 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