|
Lower right cell address in a selected range - Excel 2007
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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
Does:
Sub lastone() For Each r In Selection Set rr = r Next MsgBox (rr.Address) End Sub work any better?? -- Gary''s Student - gsnu200775 "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
This should do it for you...
With Selection.Cells MsgBox .Item(.Count).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
Jim,
I received an overflow error with your code. Thanks for responding. -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: This should do it for you... With Selection.Cells MsgBox .Item(.Count).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
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. -- Thank you for your help. MSweetG222 "Gary''s Student" wrote: Does: Sub lastone() For Each r In Selection Set rr = r Next MsgBox (rr.Address) End Sub work any better?? -- Gary''s Student - gsnu200775 "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
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? -- Thank you for your help. MSweetG222 "MSweetG222" wrote: 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. -- Thank you for your help. MSweetG222 "Gary''s Student" wrote: Does: Sub lastone() For Each r In Selection Set rr = r Next MsgBox (rr.Address) End Sub work any better?? -- Gary''s Student - gsnu200775 "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
Try this...
With Selection.Cells MsgBox .Item(.CountLarge).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: Jim, I received an overflow error with your code. Thanks for responding. -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: This should do it for you... With Selection.Cells MsgBox .Item(.Count).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
does this work if typed in the immediate window?
Debug.Print Selection.Range("A1").Offset(Selection.Rows.Count - 1, Selection.Columns.Count - 1).Address -- Gary "MSweetG222" wrote in message ... 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
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).CountLa rge).Address -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: Try this... With Selection.Cells MsgBox .Item(.CountLarge).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: Jim, I received an overflow error with your code. Thanks for responding. -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: This should do it for you... With Selection.Cells MsgBox .Item(.Count).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
That looks good to me...
-- HTH... Jim Thomlinson "MSweetG222" wrote: 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).CountLa rge).Address -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: Try this... With Selection.Cells MsgBox .Item(.CountLarge).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: Jim, I received an overflow error with your code. Thanks for responding. -- Thank you for your help. MSweetG222 "Jim Thomlinson" wrote: This should do it for you... With Selection.Cells MsgBox .Item(.Count).Address End With -- HTH... Jim Thomlinson "MSweetG222" wrote: 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 -- Thank you for your help. MSweetG222 |
Lower right cell address in a selected range - Excel 2007
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).CountLa rge).Address I think it is not properly written, but it is working. -- Thank you for your help. MSweetG222 "Gary Keramidas" wrote: does this work if typed in the immediate window? Debug.Print Selection.Range("A1").Offset(Selection.Rows.Count - 1, Selection.Columns.Count - 1).Address -- Gary "MSweetG222" wrote in message ... 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 -- Thank you for your help. MSweetG222 |
| All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com