Last cell

[hawk]

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 Jones]

Hi Hawk,

Try:

Dim rng As Range

Set rng = Cells(Rows.Count, "J").End(xlUp)

 

[hawk]

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]

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:

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
'<<=============

 

[hawk]

Norm appreciate your input, but not quite what I'm looking for.
See comments at end of lines

Roger

Sub Macro1()
depending on the amount of data in J the last cell can be different, Hope
I make myself clear.

 

[Norman Jones]

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

 

[hawk]

Perfect just what I wanted, you did grrrreat,
tks

Roger'

 

[Guest]

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!