If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 how to calculate with vba the number of records in an excel- table?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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

#1
November 10th 06, 04:36 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 André external usenet poster Posts: 4
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

#2
November 10th 06, 04:58 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 vezerid external usenet poster Posts: 751
how to calculate with vba the number of records in an excel- table?

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

#3
November 10th 06, 06:09 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 Britt external usenet poster Posts: 10
how to calculate with vba the number of records in an excel- table?

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

#4
November 10th 06, 06:57 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 vezerid external usenet poster Posts: 751
how to calculate with vba the number of records in an excel- table?

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

#5
November 11th 06, 09:38 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 Britt external usenet poster Posts: 10
how to calculate with vba the number of records in an excel- table?

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

#6
November 11th 06, 01:30 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 Dana DeLouis external usenet poster Posts: 947
how to calculate with vba the number of records in an excel- table?

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

>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Pivot table row fields limit vs. number of rows in use for Excel 2 VP needs help Excel Discussion (Misc queries) 3 October 11th 06 08:11 PM Create a Word Table from a Excel Macro Silvia Excel Discussion (Misc queries) 0 September 7th 06 12:34 AM Difficult for me, probably basic to you onlyjohn Excel Worksheet Functions 1 February 3rd 06 05:14 PM Can Excel calculate populate table using row/column values & calc's on other sheet? wildswing Excel Discussion (Misc queries) 1 January 26th 06 06:18 AM Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 08:53 PM

All times are GMT +1. The time now is 03:37 PM.