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

[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" <Acid-Sky.1u5zyf_1124712332.4491@excelforum-nospam.com> wrote in
message news:Acid-Sky.1u5zyf_1124712332.4491@excelforum-nospam.com...
>
> 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
>

[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'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

[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)


"Acid-Sky" <Acid-Sky.1u7jif_1124784321.7934@excelforum-nospam.com> wrote in
message news:Acid-Sky.1u7jif_1124784321.7934@excelforum-nospam.com...
>
> 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
>