Finding end of range in Part of Worksheet

[Nigel]

Hi All
I am trying to establish the best method of finding the last row of a range.
In a worksheet there are several tables, side by side and each takes a set
number of columns. The length of each table varies, and each can vary in
length when refreshed.
I wish to be able to act on each table separately by finding the last row of
each table.

For example table 1 occupies columns A to F; table 2 occupies columns H to
P. I do not know how many rows each table occupies but they both start in
row 1 to row n. How do I find n for each table?

Many thanks for your help

Cheers
Nigel

 

[Frank Kabel]

Hi
try something like the following for column A:
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row

and adapt this for your other columns/ranges

 

[Tom Ogilvy]

Sub Tester13()
Dim rng As Range, rng2 As Range
Dim ar As Range, Table As Range
Set rng = Cells.SpecialCells(xlConstants)

For Each ar In rng.Areas
If rng2 Is Nothing Then
Set rng2 = ar.CurrentRegion
Else
If Intersect(rng2, ar.CurrentRegion) Is Nothing Then
Set rng2 = Union(rng2, ar.CurrentRegion)
End If
End If
Next

For Each Table In rng2.Areas
MsgBox "LastRow is " & Table.Columns(1).Cells(Table.Rows.Count).Row & _
vbNewLine & " for table at location: " & Table.Address
Next
End Sub

of course you talk like these are query table results

Dim qt at QueryTable
Dim rng as Range
for each qt in Activesheet.Querytables
set rng = qt.DataRange
lastRow = rng.Columns(1).Cells(Rng.rows.count).row
Next

--
Regards,
Tom Ogilvy

Hi All
I am trying to establish the best method of finding the last row of a range.
In a worksheet there are several tables, side by side and each takes a set
number of columns. The length of each table varies, and each can vary in
length when refreshed.
I wish to be able to act on each table separately by finding the last row of
each table.

For example table 1 occupies columns A to F; table 2 occupies columns H to
P. I do not know how many rows each table occupies but they both start in
row 1 to row n. How do I find n for each table?

Many thanks for your help

Cheers
Nigel




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---

 

[Nigel]

Thanks, Frank

Hi
try something like the following for column A:
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row

and adapt this for your other columns/ranges

 

[Tom Ogilvy]

Sorry, DataRange is for pivot Tables, for querytables the entire range would
be ResultRange


Dim qt at QueryTable
Dim rng as Range
for each qt in Activesheet.Querytables
set rng = qt.ResultRange
lastRow = rng.Columns(1).Cells(Rng.rows.count).row
Next

 

[Nigel]

Thanks Tom, very comprehensive.

In fact it isn't a query table but you got me thinking about using this
method to grab my data.

The source is actually an SPSS.sav file, do you know if you can read this
directly from within Excel?

I currently save the SPSS file as an XLS then open it in Excel, the length
of this file can vary based on the number of rows in the SPSS source.

Cheers
Nigel