Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |