VBA to count rows in range and...

[gino59]

Hi all,

I have a query which dumps a data range into a worksheet. The data always takes thirteen columns and a variable number of rows. Is there a way to count the number of rows after the query returns the data range and then use that count to specify the size of an Excel 2007 table?

Right now, I'm using the following code to size the table but as you can see, the range is specified using row/column designations.

          Range("A1").Select
               ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M$2200"), , xlYes).Name = "tbl1"

It would be good to...
1) write the data to the sheet (got that part done)
2) count the number of rows used in the sheet
3) size the table as A1:Mxxxx where xxxx is the variable number found by counting the range rows.

Many thanks as always!

Cheers,
Gino

 

[Zardax]

Hi,
There's a couple ways, if you have the range as an object then you can use 'range.rows.count' - allow for the header rows too.
Alternatively you can get the last used row, beware though that this is only the last used row for the selected column.
Range("A65536").End(xlUp).Row

 

[Jonmo1]

Here's a common mothod for that..

Dim LR As Long
 
LR = Cells(Rows.Count, "A").End(xlup).Row
Range("A1:M" & LR)

Hope that helps.

 

[gino59]

Sweet!!!

Thanks all y'all!!!

Auto-sizing tables was driving me nuts!

Many thanks!

Cheers!
Gino

 

[Zardax]

Ha, Jonmo1 I like it especially as it will work for excel 2003 and 2007 with it's non 65,000 row limit.