count number of rows (variable range)

[Acid-Sky]

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

 

[Bob Phillips]

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]

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 :

 

[Bob Phillips]

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)