Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Get number of rows that data uses, including blank rows

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)

Thanks in advance for your help.

Regards,

Denham.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Get number of rows that data uses, including blank rows


see attachment for one solution


+-------------------------------------------------------------------+
|Filename: rowcounter1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5228 |
+-------------------------------------------------------------------+

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572977

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Get number of rows that data uses, including blank rows

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 |
|Download: http://www.excelforum.com/attachment.php?postid=5228 |
+-------------------------------------------------------------------+

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572977


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Get number of rows that data uses, including blank rows


it is a zipped excel.xls file


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572977

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Get number of rows that data uses, including blank rows

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.


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Get number of rows that data uses, including blank rows


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
View this thread: http://www.excelforum.com/showthread...hreadid=572977

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
How to number rows after data has been filtered? Maria Excel Discussion (Misc queries) 3 August 26th 05 02:57 PM
formula to use when number of rows changes dynamically confused Excel Worksheet Functions 3 August 17th 05 03:55 PM
Removing blank rows in a worksheet Louise Excel Worksheet Functions 6 May 26th 05 02:21 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 10:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"