Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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


  #2   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 06: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 07:18 AM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 09:53 PM


All times are GMT +1. The time now is 05:34 PM.

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

About Us

"It's about Microsoft Excel"