Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic chart on user selected data range in Excel 2007 | Charts and Charting in Excel | |||
how do I insert the address of a selected cell into a fixed cell | Excel Discussion (Misc queries) | |||
Get selected cell name/address | Excel Programming | |||
How do I convert a selected Cell address in a Range to Values? | Excel Programming | |||
How do you get a Selected Range address into a variable? | Excel Programming |