count rows?

[xq]

it was working, but not now, i couldn't figure out why,
i first have to pull out data from database by using querytable, refresh to worksheet, then count how many rows there r on the worksheet. but the number i got is 1 (there r 220 rows actually), why?
here's the code:

txt_sql = " SELECT ......"

Dim objQT As QueryTable
With Worksheets("Sheet1&quot.QueryTables.Add(Connection:=X, _
Destination:=Range("A1&quot, Sql:=txt_sql)
.Refresh
End With

Dim numofRows As Integer
numofRows = Sheets("Sheet1&quot.UsedRange.Rows.Count

the code shouldnt' be wrong, and the rest of code shouldn't be either, is there any other reason may cause that problem? really appreciate if anybody could help me out.

 

[xlbo]

Hi xq,
UsedRange is fairly tempremental.
Either use
Dim numofRows As LONG
sheets("Sheet1&quot.usedrange 'This resets the usedrange
numofRows = Sheets("Sheet1&quot.UsedRange.Rows.Count

or
Dim numofRows As LONG
numofRows = Sheets("Sheet1&quot.range("A65536&quot.end(xlup).row

DON'T use integer for a lastrow calculation - integers only go up to about 37000 or so and there are 65536 rows in a worksheet. If your last row is to high, it'll throw what is technically known as a wobbler
HTH
~Geoff~

 

[xq]

thanks million!