# Get number of rows that data uses, including blank rows

=?Utf-8?B?RGVuaGFtIENvb3Rl?=
Guest
Posts: n/a

 18th Aug 2006
I have a column which contains numeric values. Some rows may NOT contain a
value (ie: they're blank).

Short of filling in all the blanks with 0's, how might I be able to get the
ROW number of the final data entry?

To explain further, I am using the following formula:

=SUM(OFFSET(A1,0,0,COUNTA(A:A)))

This allows me to sum all the values in column A, using A1 as a reference
point, and making the range depth the value of the non-blank cells found by
COUNTA. Problem is, if there are blank values in between non-blank values,
the offset doesn't include those, and therefore the range depth is smaller
than the row number of the final data entry.

Hopefully, by obtaining the ROW number, I'll be able to specify that as the
offset value, as opposed to using COUNTA().

I hope I've put this clearly enough. (I've tried to keep the concept basic -
the actual formula that it will apply to is way to long and beyond the scope
of this posting)

Regards,

Denham.

robert111
Guest
Posts: n/a

 18th Aug 2006

see attachment for one solution

+-------------------------------------------------------------------+
|Filename: rowcounter1.zip |
+-------------------------------------------------------------------+

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996

=?Utf-8?B?RGVuaGFtIENvb3Rl?=
Guest
Posts: n/a

 18th Aug 2006
Aargh - that gives me an "Invalid Attachment specified" error.

Do you perhaps have a direct link to the file, or perhaps the forum article
number?

Thanks.

"robert111" wrote:

>
> see attachment for one solution
>
>
> +-------------------------------------------------------------------+
> |Filename: rowcounter1.zip |
> +-------------------------------------------------------------------+
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
>
>

robert111
Guest
Posts: n/a

 18th Aug 2006

it is a zipped excel.xls file

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996

=?Utf-8?B?RGVuaGFtIENvb3Rl?=
Guest
Posts: n/a

 18th Aug 2006
OK, I've taken a look at the attachment. This is, unfortunately, not quite
what I'm looking for.

To be more specific, I'm looking at using a dynamic value in the OFFSET()
funtion, which I wish to obtain by getting the NUMBER of the final ROW in
which data is entered, regardless of blank cells.

Thanks again,

Denham.

robert111
Guest
Posts: n/a

 22nd Aug 2006

but my solution gives you that,ie the row number of the last cell in the
column that has data in it

I numbered the rows purposely to give you a row number. It is the cell
reference that I have enlarged that you need to put in your offset
statement.

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?bmNvY2hyYXg=?= Microsoft Excel Misc 2 27th Jun 2007 04:40 AM =?Utf-8?B?RGVuaGFtIENvb3Rl?= Microsoft Excel Worksheet Functions 4 21st Aug 2006 09:18 AM =?Utf-8?B?RGVuaGFtIENvb3Rl?= Microsoft Excel Worksheet Functions 0 18th Aug 2006 12:25 PM =?Utf-8?B?SDBNRUxZ?= Microsoft Excel Misc 0 30th Mar 2006 08:11 PM =?Utf-8?B?U3VnYXJ0aGViZWV0?= Microsoft Excel Programming 5 24th Jan 2006 07:43 PM

Features