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