Set an Object
 

I'm familiar with setting an Object under UseRangeObject1() (first below)

Sub UseRangeObject1()
Lr = Range("M" & Rows.Count).End(xlUp).Row
Set rng = Range("M5:M" & Lr)
End Sub

But can't one bypass getting the Lr as Follows? Currently no working -
what is wrong with syntax?

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

TIA,

Jim

Set an Object
 

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

Change to:


Sub UseRangeObject2()
Set rng = Range("M5:M" & Cells(Rows.Count, 1).End(xlUp).Row)
End Sub

You need a cell reference for the End function to work off of. It won't do
it off of just the rows.count because that returns an integer rather than a
cell reference.



"JMay" wrote in message
...
I'm familiar with setting an Object under UseRangeObject1() (first below)

Sub UseRangeObject1()
Lr = Range("M" & Rows.Count).End(xlUp).Row
Set rng = Range("M5:M" & Lr)
End Sub

But can't one bypass getting the Lr as Follows? Currently no working -
what is wrong with syntax?

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

TIA,

Jim

Set an Object
 

Almost the

Sub UseRangeObject2()
Set rng = Range("M5:M" & Cells(Rows.Count, "M").End(xlUp).Row)
MsgBox rng.Address
End Sub
--
Gary''s Student - gsnu200909


"JMay" wrote:

I'm familiar with setting an Object under UseRangeObject1() (first below)

Sub UseRangeObject1()
Lr = Range("M" & Rows.Count).End(xlUp).Row
Set rng = Range("M5:M" & Lr)
End Sub

But can't one bypass getting the Lr as Follows? Currently no working -
what is wrong with syntax?

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

TIA,

Jim

Set an Object
 

Thanks Gary.

Are there other options in this case?

Jim

"Gary''s Student" wrote:

Almost the

Sub UseRangeObject2()
Set rng = Range("M5:M" & Cells(Rows.Count, "M").End(xlUp).Row)
MsgBox rng.Address
End Sub
--
Gary''s Student - gsnu200909


"JMay" wrote:

I'm familiar with setting an Object under UseRangeObject1() (first below)

Sub UseRangeObject1()
Lr = Range("M" & Rows.Count).End(xlUp).Row
Set rng = Range("M5:M" & Lr)
End Sub

But can't one bypass getting the Lr as Follows? Currently no working -
what is wrong with syntax?

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

TIA,

Jim

Set an Object
 

Well, if I understand what you are asking correctly, you could do the Set
operation this way...

Set = Range("M5").Resize(Cells(Rows.Count, "M").End(xlUp).Row - 4)

where the -4 comes from subtracting the start row of 5 and adding 1 because
we want the count of rows and not just the difference of rows.

--
Rick (MVP - Excel)


"JMay" wrote in message
...
Thanks Gary.

Are there other options in this case?

Jim

"Gary''s Student" wrote:

Almost the

Sub UseRangeObject2()
Set rng = Range("M5:M" & Cells(Rows.Count, "M").End(xlUp).Row)
MsgBox rng.Address
End Sub
--
Gary''s Student - gsnu200909


"JMay" wrote:

I'm familiar with setting an Object under UseRangeObject1() (first
below)

Sub UseRangeObject1()
Lr = Range("M" & Rows.Count).End(xlUp).Row
Set rng = Range("M5:M" & Lr)
End Sub

But can't one bypass getting the Lr as Follows? Currently no
working -
what is wrong with syntax?

Sub UseRangeObject2()
Set rng = Range("M5:M" & Rows.Count.End(xlUp).Row)
End Sub

TIA,

Jim