Issue with setting Range

[Guest]

Hi,

I'm trying to set a range to be equal to cell D2 through the last cell in
the column with a value (prior code ensures there will be no empty cells
between cell D2 and the last cell in column D with a value). I've tried the
following code which is not working...any idea how to do this?

With Sheets("Member ID Report Master")
Set Q = .Range("D2")
Set salesrange = .Range(Q, Q.End(x1down))
End With

 

[JE McGimpsey]

One way:

With Sheets("Member ID Report Master")
Set salesrange = .Range(.Cells(2, 4), _
.Cells(.Rows.Count, 4).End(xlUp))
End With

 

[Ron Coderre]

Hi, robs3131

x1down should be xlDown
The 2nd character is the letter "L", not the number 1 (one),

Um....if that wasn't a typo.....then you just got an object lesson in why
you should ALWAYS put Option Explicit at the top of every module. Without
that, every variable that you misspell gets created on-the-fly, instead of
being flagged as an error. With Option Explicit, you cannot use any
variable that is not defined beforehand.

Does that help?

 

[Jim Rech]

It's xlDown not x1Down (EL vs One).