I have a spreadsheet that may change in size daily. How can I refer to the
range in VBA? CurrentRegion doesn't work as there may be empty rows or
columns in the range I want to select.

Looking for something that would give me (A1:"bottom right cell")

Thanks for any ideas.

Jamie

Jamie,
ActiveSheet.UsedRange should give you what you need.
Jim Cone
San Francisco, USA

"Jamie" wrote in message
om
I have a spreadsheet that may change in size daily. How can I refer to the
range in VBA? CurrentRegion doesn't work as there may be empty rows or
columns in the range I want to select.
Looking for something that would give me (A1:"bottom right cell")
Thanks for any ideas.
Jamie

Thanks! That seems to do it.

Jamie

"Jim Cone" wrote in message
...
Jamie,
ActiveSheet.UsedRange should give you what you need.
Jim Cone
San Francisco, USA

"Jamie" wrote in message
om
I have a spreadsheet that may change in size daily. How can I refer to the
range in VBA? CurrentRegion doesn't work as there may be empty rows or
columns in the range I want to select.
Looking for something that would give me (A1:"bottom right cell")
Thanks for any ideas.
Jamie

Jamie,

If you want to pinpoint the area with data in it then try the following function.
The UsedRange can sometimes encompass blank rows/columns...
Jim Cone
San Francisco, USA
'-----------------------------------

Function BottomRightCorner(ByRef objSheet As Excel.Worksheet) As Excel.Range
' Jim Cone
'Returns a range object.
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long

' Optional line below could go in the calling sub,
' or a check for hidden cells could be done in the calling sub.
If objSheet.FilterMode Then objSheet.ShowAllData
BottomRow = objSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastColumn = objSheet.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set BottomRightCorner = objSheet.Cells(BottomRow, LastColumn)
Exit Function

NoCorner:
Beep
Set BottomRightCorner = objSheet.Cells(1, 1)
End Function
'-----------------

'Call the function like this
Sub GetLastCellWithData()
Dim rngCell As Excel.Range
Set rngCell = BottomRightCorner(ActiveSheet)
MsgBox rngCell.Address 'for display purposes
Set rngCell = Nothing
End Sub
'-----------


"Jamie" wrote in message ...
Thanks! That seems to do it.
Jamie


"Jim Cone"
wrote in message
...
Jamie,
ActiveSheet.UsedRange should give you what you need.
Jim Cone
San Francisco, USA


"Jamie" wrote in message
om
I have a spreadsheet that may change in size daily. How can I refer to the
range in VBA? CurrentRegion doesn't work as there may be empty rows or
columns in the range I want to select.
Looking for something that would give me (A1:"bottom right cell")
Thanks for any ideas.
Jamie

Unfortunately, this doesn't always work when you have some merged
cells. I have seen it return column A when other columns have data.

Any ideas?

John