how to calculate with vba the number of records in an excel- table?

Hi,

I need the number of records in a table, and i need it into a variable in
VBA.

Thanks again
Britt

One way (using worksheet function):

Dim NumRec as Long
....
NumRec =
Application.WorksheetFunction.COUNTA(Sheets("Data" ).Range("A:A"))

HTH
Kostis Vezerides

André wrote:
> Hi,
>
> I need the number of records in a table, and i need it into a variable in
> VBA.
>
> Thanks again
> Britt

Hi, thanks for replying.

My problem is that i never know the range.
Suppose table "mydata" with the fieldrow in A5:F5 and the real records from
A6:F6. But the number of records will be different each time (those records
are coming from an external source).

So i need something like counting the rows of "mytable", but without using a
given range because it varies each time.

Thanks





"vezerid" > schreef in bericht
oups.com...
One way (using worksheet function):

Dim NumRec as Long
....
NumRec =
Application.WorksheetFunction.COUNTA(Sheets("Data" ).Range("A:A"))

HTH
Kostis Vezerides

André wrote:
> Hi,
>
> I need the number of records in a table, and i need it into a variable in
> VBA.
>
> Thanks again
> Britt

The solution that I gave you caters exactly to this specification. If
sheet name and columns are fixed then COUNTA(A:A) will examine the
entire column and see how many records you have.

Don't forget to subtract, if necessary, the number of items in column
A:A that are not part of the table. This includes the field heading in
A5 as well as possible other items in A1:A4.

HTH
Kostis Vezerides


Britt wrote:
> Hi, thanks for replying.
>
> My problem is that i never know the range.
> Suppose table "mydata" with the fieldrow in A5:F5 and the real records from
> A6:F6. But the number of records will be different each time (those records
> are coming from an external source).
>
> So i need something like counting the rows of "mytable", but without using a
> given range because it varies each time.
>
> Thanks
>
>
>
>
>
> "vezerid" > schreef in bericht
> oups.com...
> One way (using worksheet function):
>
> Dim NumRec as Long
> ...
> NumRec =
> Application.WorksheetFunction.COUNTA(Sheets("Data" ).Range("A:A"))
>
> HTH
> Kostis Vezerides
>
> André wrote:
> > Hi,
> >
> > I need the number of records in a table, and i need it into a variable in
> > VBA.
> >
> > Thanks again
> > Britt

Indeed, thanks

"vezerid" > schreef in bericht
ups.com...
The solution that I gave you caters exactly to this specification. If
sheet name and columns are fixed then COUNTA(A:A) will examine the
entire column and see how many records you have.

Don't forget to subtract, if necessary, the number of items in column
A:A that are not part of the table. This includes the field heading in
A5 as well as possible other items in A1:A4.

HTH
Kostis Vezerides


Britt wrote:
> Hi, thanks for replying.
>
> My problem is that i never know the range.
> Suppose table "mydata" with the fieldrow in A5:F5 and the real records
> from
> A6:F6. But the number of records will be different each time (those
> records
> are coming from an external source).
>
> So i need something like counting the rows of "mytable", but without using
> a
> given range because it varies each time.
>
> Thanks
>
>
>
>
>
> "vezerid" > schreef in bericht
> oups.com...
> One way (using worksheet function):
>
> Dim NumRec as Long
> ...
> NumRec =
> Application.WorksheetFunction.COUNTA(Sheets("Data" ).Range("A:A"))
>
> HTH
> Kostis Vezerides
>
> André wrote:
> > Hi,
> >
> > I need the number of records in a table, and i need it into a variable
> > in
> > VBA.
> >
> > Thanks again
> > Britt

Hi. I may be wrong, but in your external data link, I believe the range
name will grow/shrink as the size of your data changes. Check the option
"If the number of rows in the data range changes upon refresh: "

Maybe something like this...

Dim NumRec As Long
'// Don't count the Heading Row
NumRec = Range("MyData").Rows.Count - 1

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"Britt" > wrote in message
...
> Indeed, thanks
>
> "vezerid" > schreef in bericht
> ups.com...
> The solution that I gave you caters exactly to this specification. If
> sheet name and columns are fixed then COUNTA(A:A) will examine the
> entire column and see how many records you have.
>
> Don't forget to subtract, if necessary, the number of items in column
> A:A that are not part of the table. This includes the field heading in
> A5 as well as possible other items in A1:A4.
>
> HTH
> Kostis Vezerides
>
>
> Britt wrote:
>> Hi, thanks for replying.
>>
>> My problem is that i never know the range.
>> Suppose table "mydata" with the fieldrow in A5:F5 and the real records
>> from
>> A6:F6. But the number of records will be different each time (those
>> records
>> are coming from an external source).
>>
>> So i need something like counting the rows of "mytable", but without
>> using a
>> given range because it varies each time.
>>
>> Thanks
>>
>>
>>
>>
>>
>> "vezerid" > schreef in bericht
>> oups.com...
>> One way (using worksheet function):
>>
>> Dim NumRec as Long
>> ...
>> NumRec =
>> Application.WorksheetFunction.COUNTA(Sheets("Data" ).Range("A:A"))
>>
>> HTH
>> Kostis Vezerides
>>
>> André wrote:
>> > Hi,
>> >
>> > I need the number of records in a table, and i need it into a variable
>> > in
>> > VBA.
>> >
>> > Thanks again
>> > Britt
>
>