Getting Compile error - on this line

J

Jim May

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?
 
J

John Bundy

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.
 
B

bpeltzer

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 )
 
J

Jim May

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 & ")"
 
J

Jim May

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
 
B

bpeltzer

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
 
D

Dave Peterson

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