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

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

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

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

---------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------

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

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

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