selecting an area with VBA

[David Gerstman]

<a
href="http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us">Earlier</a> I asked about defining a range for non-contiguous data.

The answer involved going to the bottom of the page and then using xlUp to
hit the last row of data. Like this:

Set poc_range = ActiveSheet.Range("b2", Cells(Rows.Count, 2).End(xlUp))

Now what I'd like to do is something similar. I'd like to be able to get a
range that covers <strong>both</strong> the rows and columns and then select
it. Is that possible, or can I only specify one direction at a time?

David

 

[PCLIVE]

Maybe something like this.

Set poc_range = ActiveSheet.Range("b2", ActiveCell.SpecialCells(xlLastCell))

Regards,
Paul

 

[Bob Phillips]

Sub LastCell()
Dim poc_range As Range

Set poc_range = ActiveSheet.Range("B2").Resize(LastRow - 1, LastCol - 1)

poc_range.Select
End Sub


'-----------------------------------------------------------------
Function LastRow() As Long
'-----------------------------------------------------------------
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function

'-----------------------------------------------------------------
Function LastCol() As Long
'-----------------------------------------------------------------
LastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End Function





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<a

href="http://msdn.microsoft.com/newsgroups/default.aspx?query=gerstman&dg=mi
crosoft.public.excel.programming&cat=en-us-msdn-officedev-excel&lang=en&cr=U
S&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&catlist=B7714BAA-0D60-40B0-A226-8B
9CF33299A5%2C774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=