Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

Try putting spaces on both sides of your first &. Typically to make
troubleshooting easier, set a variable to the value then use it like this:

LastRow=Cells(rows.count,"D").end(xlUp).row
Set rng = Range("D2:D" & LastRow & ")"

just all around easier that way.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

John - Thanks, but I'm still getting Compile error with your recommended
changes
made - here is my latest code

Dim rng As Range
Dim Lrow As Integer
Lrow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Range("D2:D & Lrow & ")"

"John Bundy" wrote:

Try putting spaces on both sides of your first &. Typically to make
troubleshooting easier, set a variable to the value then use it like this:

LastRow=Cells(rows.count,"D").end(xlUp).row
Set rng = Range("D2:D" & LastRow & ")"

just all around easier that way.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

try this
Set rng = Range("D2:D" & Lrow)
or
rng = Range("D2:D" & Lrow)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim May" wrote in message
...
John - Thanks, but I'm still getting Compile error with your recommended
changes
made - here is my latest code

Dim rng As Range
Dim Lrow As Integer
Lrow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Range("D2:D & Lrow & ")"

"John Bundy" wrote:

Try putting spaces on both sides of your first &. Typically to make
troubleshooting easier, set a variable to the value then use it like
this:

LastRow=Cells(rows.count,"D").end(xlUp).row
Set rng = Range("D2:D" & LastRow & ")"

just all around easier that way.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate
window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

Not sure about the & ")" at the end... I think you just need the closing
paren, since the paren isn't part of the string argument of the range
function:
Set rng = Range("D2:D" & cells(rows.count,"D").end(xlUp).row )


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

Thanks bpeltzer. I have seen (before) instances where it was neccesary to end
such a range with the ")". Can you explain why/when this may be necessary?
Tks,
Jim

"bpeltzer" wrote:

Not sure about the & ")" at the end... I think you just need the closing
paren, since the paren isn't part of the string argument of the range
function:
Set rng = Range("D2:D" & cells(rows.count,"D").end(xlUp).row )


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

I generally look at how I want the result to read (often by using the
immediate window as you suggested). In this instance, the result should read
Range("D2:D9"). I think your original formula generated Range("D2:D9)
--Bruce

"Jim May" wrote:

Thanks bpeltzer. I have seen (before) instances where it was neccesary to end
such a range with the ")". Can you explain why/when this may be necessary?
Tks,
Jim

"bpeltzer" wrote:

Not sure about the & ")" at the end... I think you just need the closing
paren, since the paren isn't part of the string argument of the range
function:
Set rng = Range("D2:D" & cells(rows.count,"D").end(xlUp).row )


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

I bet you've seen & ")" when you were building a formula that would be plopped
into a cell.



Jim May wrote:

Thanks bpeltzer. I have seen (before) instances where it was neccesary to end
such a range with the ")". Can you explain why/when this may be necessary?
Tks,
Jim

"bpeltzer" wrote:

Not sure about the & ")" at the end... I think you just need the closing
paren, since the paren isn't part of the string argument of the range
function:
Set rng = Range("D2:D" & cells(rows.count,"D").end(xlUp).row )


"Jim May" wrote:

Set rng = Range("D2:D"&Cells(rows.count,"D").end(xlUp).row & ")"

When I extract only
Cells(rows.count,"D").end(xlUp).row and drop it in the Immediate window
preceeded with a ?

I get 9 << the correct row number

Can someone assist?

--

Dave Peterson