Last cell
Trying to make a macro that will find the last cell, that can vary, in
colum J but having trouble getting my brain around how to do it. Any help appreciated. Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" 'Selection.End(xlDown).Select Range("A1:J68").Select Range("J68").Activate End Sub |
Last cell
Hi Hawk,
Try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) --- Regards, Norman "hawk" wrote in message news:Arfzf.256935$2k.125854@pd7tw1no... Trying to make a macro that will find the last cell, that can vary, in colum J but having trouble getting my brain around how to do it. Any help appreciated. Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" 'Selection.End(xlDown).Select Range("A1:J68").Select Range("J68").Activate End Sub |
Last cell
Norman, tried the following but no work...
-------------------------------------------------------------------------------------- Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select 'Range("A1:J68").Select Range("J68").Activate End Sub ---------------------------------------------------------------------------------------- "Norman Jones" wrote in message ... Hi Hawk, Try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) --- Regards, Norman "hawk" wrote in message news:Arfzf.256935$2k.125854@pd7tw1no... Trying to make a macro that will find the last cell, that can vary, in colum J but having trouble getting my brain around how to do it. Any help appreciated. Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" 'Selection.End(xlDown).Select Range("A1:J68").Select Range("J68").Activate End Sub |
Last cell
Hi Hawk,
It is not clear (to me) what you are trying to do. The suggested code returns the last populated cell in column J, in accordance with your request: Trying to make a macro that will find the last cell, that can vary, in colum J If your intention is to select that cell, then try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) rng.select However, it is rarely necessary, and usually inefficient, to make such selections. Instead, try something like: '============= Public Sub Tester() Dim rng As Range 'Define last populated cell in column J Set rng = Cells(Rows.Count, "J").End(xlUp) 'Do something with the range object, e.g.: rng.Interior.ColorIndex = 6 End Sub '<<============= --- Regards, Norman "hawk" wrote in message news:JSfzf.373982$ki.20044@pd7tw2no... Norman, tried the following but no work... --------------------------------------------------------------------------------- Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select 'Range("A1:J68").Select Range("J68").Activate End Sub --------------------------------------------------------------------------------- |
Last cell
Norm appreciate your input, but not quite what I'm looking for.
See comments at end of lines Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select <=== thsi selects the last cell with values in Col J 'Range("A1:J68").Select <=== This selects range A1:j68 Range("J68").Activate **** what I need is to be able to have the selection vary each time it is run. depending on the amount of data in J the last cell can be different, Hope I make myself clear. End Sub "Norman Jones" wrote in message ... Hi Hawk, It is not clear (to me) what you are trying to do. The suggested code returns the last populated cell in column J, in accordance with your request: Trying to make a macro that will find the last cell, that can vary, in colum J If your intention is to select that cell, then try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) rng.select However, it is rarely necessary, and usually inefficient, to make such selections. Instead, try something like: '============= Public Sub Tester() Dim rng As Range 'Define last populated cell in column J Set rng = Cells(Rows.Count, "J").End(xlUp) 'Do something with the range object, e.g.: rng.Interior.ColorIndex = 6 End Sub '<<============= --- Regards, Norman "hawk" wrote in message news:JSfzf.373982$ki.20044@pd7tw2no... Norman, tried the following but no work... --------------------------------------------------------------------------------- Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select 'Range("A1:J68").Select Range("J68").Activate End Sub --------------------------------------------------------------------------------- |
Last cell
Hi Roger,
Perhaps I am being overly obtuse, but your intent is still not apparent to me but, as a myopic stab, try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) Range(Range("A1"), rng).Select --- Regards, Norman "hawk" wrote in message news:basAf.299205$2k.129089@pd7tw1no... Norm appreciate your input, but not quite what I'm looking for. See comments at end of lines Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select <=== thsi selects the last cell with values in Col J 'Range("A1:J68").Select <=== This selects range A1:j68 Range("J68").Activate **** what I need is to be able to have the selection vary each time it is run. depending on the amount of data in J the last cell can be different, Hope I make myself clear. End Sub |
Last cell
Perfect just what I wanted, you did grrrreat,
tks Roger' "Norman Jones" wrote in message ... Hi Roger, Perhaps I am being overly obtuse, but your intent is still not apparent to me but, as a myopic stab, try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) Range(Range("A1"), rng).Select --- Regards, Norman "hawk" wrote in message news:basAf.299205$2k.129089@pd7tw1no... Norm appreciate your input, but not quite what I'm looking for. See comments at end of lines Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) 'Selection.End(xlDown).Select <=== thsi selects the last cell with values in Col J 'Range("A1:J68").Select <=== This selects range A1:j68 Range("J68").Activate **** what I need is to be able to have the selection vary each time it is run. depending on the amount of data in J the last cell can be different, Hope I make myself clear. End Sub |
Last cell
Okay, now take this one step further. How do you dynamicly get both the last
column and row for a spreadsheet that can change in size for both values? I'm using this functionality to create a dynamic Pivot Table for varying spreadsheets. Thanks! "Norman Jones" wrote: Hi Hawk, Try: Dim rng As Range Set rng = Cells(Rows.Count, "J").End(xlUp) --- Regards, Norman "hawk" wrote in message news:Arfzf.256935$2k.125854@pd7tw1no... Trying to make a macro that will find the last cell, that can vary, in colum J but having trouble getting my brain around how to do it. Any help appreciated. Roger Sub Macro1() ' ' Application.Goto Reference:="R5C8" 'Selection.End(xlDown).Select Range("A1:J68").Select Range("J68").Activate End Sub |
All times are GMT +1. The time now is 04:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com