A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
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?



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


Ads
  #2  
Old November 10th 06, 04:58 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
vezerid
external usenet poster
 
Posts: 751
Default 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  
Old November 10th 06, 06:09 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
Britt
external usenet poster
 
Posts: 10
Default how to calculate with vba the number of records in an excel- table?

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



  #4  
Old November 10th 06, 06:57 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
vezerid
external usenet poster
 
Posts: 751
Default 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:
> 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


  #5  
Old November 11th 06, 09:38 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
Britt
external usenet poster
 
Posts: 10
Default 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:
> 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



  #6  
Old November 11th 06, 01:30 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming
Dana DeLouis
external usenet poster
 
Posts: 947
Default 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:
>> 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

>
>



 




Thread Tools
Display Modes

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

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 04:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.