Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Pivot table row fields limit vs. number of rows in use for Excel 2  Excel Discussion (Misc queries)  
Create a Word Table from a Excel Macro  Excel Discussion (Misc queries)  
Difficult for me, probably basic to you  Excel Worksheet Functions  
Can Excel calculate populate table using row/column values & calc's on other sheet?  Excel Discussion (Misc queries)  
Does Excel 2000 have a 'datedif' function to calculate the number.  Excel Worksheet Functions 