set a range to non-blank cells within a range?

[danno79]

I'm trying to set a range equal to the nonblank cells within range I81 and I111 (number of no blanks changes). Cells with data in are always at the top of the range with no blanks separating.

At the moment I am setting the range by manually altering the last cell in the range any ideas how to achieve this automatically greatly apreciated.

Set rSrc = Range("l80:(last non blank cell up to l111)")

 

[lenze]

Set rSrc= Range("I81:I" & Cells(Rows.Count,"I").End(xlUp).Row)

Or maybe you need

Set rSrc= Range("I81:I" & Range("I1111").End(xlUp).Row)

lenze

 

[danno79]

Thanks lenze the first worked just fine for me - i figured there was a fairly simple way to do it just wasn't sure of the syntax