Lower right cell address in a selected range - Excel 2007

M

MSweetG222

I am trying to determine the cell address of the cell in the lower right
corner of the selected range. I tried the code from David McRitchie's
website (modified slightly, I changed code from denoted address on the
spreadsheet to displaying address in a msgbox). The code works okay in Excel
2003, but when selecting a larger region in Excel 2007, I receive an overflow
issue.

Assume the range selected is A:FFF

Sub MarkSepAreas()
'David McRitchie 1999-06-03 Mark cells with cell address and
'area number. Enhanced based on Alan Beban code 1999-06-03.
'Documented with Join()
'in http://www.mvps.org/dmcritchie/excel/join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Count
x = Selection.Areas(i)(j).AddressLocal(0, 0)
MsgBox x
Next
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
G

Gary''s Student

Does:

Sub lastone()
For Each r In Selection
Set rr = r
Next
MsgBox (rr.Address)
End Sub


work any better??
 
J

Jim Thomlinson

This should do it for you...

With Selection.Cells
MsgBox .Item(.Count).Address
End With
 
M

MSweetG222

Gary''s Student,

I ran your code.

While I did not receive an overflow error, it has been 3 minutes and the
code is still running.

I will wait a while longer to see if it produces a correct result.
 
M

MSweetG222

Hello Gary''s Student,

The program has been running for about 15 minutes.
I think there must be too many rows in Excel 2007?

Any other thoughts?
 
G

Gary Keramidas

does this work if typed in the immediate window?

Debug.Print Selection.Range("A1").Offset(Selection.Rows.Count - 1,
Selection.Columns.Count - 1).Address
 
M

MSweetG222

Jim,

Thank you. Your info help.

Here is what I wrote. It accounts for filtered and hidden rows/columns.
Have I written it properly or could it be improved upon?

Dim i As Long
i = Selection.Areas.Count
MsgBox Selection.Areas(i).Item(Selection.Areas(i).CountLarge).Address
 
M

MSweetG222

Hello Gary,

I tried your code.
It stops with the last cell just before the 1st filtered/hidden row.

I posted the following in reply to Jim's post (earlier in tread).

Dim i As Long
i = Selection.Areas.Count
MsgBox Selection.Areas(i).Item(Selection.Areas(i).CountLarge).Address

I think it is not properly written, but it is working.

--
Thank you for your help.

MSweetG222


Gary Keramidas said:
does this work if typed in the immediate window?

Debug.Print Selection.Range("A1").Offset(Selection.Rows.Count - 1,
Selection.Columns.Count - 1).Address
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top