Set an Object

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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
 
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.
 
Almost there:

Sub UseRangeObject2()
Set rng = Range("M5:M" & Cells(Rows.Count, "M").End(xlUp).Row)
MsgBox rng.Address
End Sub
 
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.
 
Back
Top