count number of rows (variable range)
 

Hi,

well, needless to say that I am quite new to VBA... anyway.

I have a worksheet containing lots of data and to avoid people enterin
crap created a VBA Macro showing a GUI so that new valid data can b
entered.

every row has a unique number consiting of year+month+number; i.e
200508003 for the third entry in August 2005.

As every entry adds a new row to the worksheet in question i need som
way to
read all rows in that worksheet and filter and count the ones matchin
the actual month to generate a new valid ID.

well... i tried lots of range, count etc already but can't even devis
a way for this growing number of rows... any ideas? posts that ar
helpful?

thanks

--
Acid-Sk
-----------------------------------------------------------------------
Acid-Sky's Profile: http://www.excelforum.com/member.php...fo&userid=2650
View this thread: http://www.excelforum.com/showthread.php?threadid=39771

count number of rows (variable range)
 

Here is a formula to calculate it

=MAX(IF(LEFT(A1:A100,6)="200508",A1:A100))+1

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Acid-Sky" wrote in
message ...

Hi,

well, needless to say that I am quite new to VBA... anyway.

I have a worksheet containing lots of data and to avoid people entering
crap created a VBA Macro showing a GUI so that new valid data can be
entered.

every row has a unique number consiting of year+month+number; i.e.
200508003 for the third entry in August 2005.

As every entry adds a new row to the worksheet in question i need some
way to
read all rows in that worksheet and filter and count the ones matching
the actual month to generate a new valid ID.

well... i tried lots of range, count etc already but can't even devise
a way for this growing number of rows... any ideas? posts that are
helpful?

thanks!


--
Acid-Sky
------------------------------------------------------------------------
Acid-Sky's Profile:
http://www.excelforum.com/member.php...o&userid=26502
View this thread: http://www.excelforum.com/showthread...hreadid=397715

count number of rows (variable range)
 

Hi,

I think I understand your answer and it is perfect for using the cells
but it is not exactly what I am trying to do. Furthermore it doesn'
take into account that I just don't know how many rows there are (a
their numbe ris dynamic). Could be 10 rows or 20.000...

And how can I use this in VBA?

Thanks :

--
Acid-Sk
-----------------------------------------------------------------------
Acid-Sky's Profile: http://www.excelforum.com/member.php...fo&userid=2650
View this thread: http://www.excelforum.com/showthread.php?threadid=39771

count number of rows (variable range)
 

cRows = Cells(Rows.Count,"A").End(xlUp).Row

so the next free nrow would be cRows+1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Acid-Sky" wrote in
message ...

Hi,

I think I understand your answer and it is perfect for using the cells,
but it is not exactly what I am trying to do. Furthermore it doesn't
take into account that I just don't know how many rows there are (as
their numbe ris dynamic). Could be 10 rows or 20.000...

And how can I use this in VBA?

Thanks :)


--
Acid-Sky
------------------------------------------------------------------------
Acid-Sky's Profile:
http://www.excelforum.com/member.php...o&userid=26502
View this thread: http://www.excelforum.com/showthread...hreadid=397715