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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Indeed, thanks
>
> "vezerid" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> schreef in bericht
>> news:(E-Mail Removed)...
>> 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
>
>
|
Similar Threads
