Quotes within quotes format problem

[El_Diabolo]

Hi All,

I have been trying all day to change the format of an existing (successful) statement so that it uses a variable as part of the range definition, rather than being hard-coded. Can't seem to do it. All rather sad. The statement in question is:

 If Not Intersect(Target, Range("H8:OFFSET(H8,0,0,COUNTA(H:H),1)")) Is Nothing Then

So instead of having "H8" I would like "H" & FRow, for example. I use this format regularly, but seem unable to manage with the above. Any help would be much appreciated.

Best regards.

[mancubus]

hi. try this:

Range("H8:H" & Cells(Rows.Count, "H").End(xlUp).Row)

or

Range("H" & FRow & ":H" & Cells(Rows.Count, "H").End(xlUp).Row)

[El_Diabolo]

Thank you, Mancubus. That works perfectly. I would just love to have found the solution using the "OFFSET" format. Now that I have your solution I can spend some free time trying to find the answer.
My enemy is me. Not brave and strong. Ergo, I feel ashamed. Nothing new there.

Best regards.

[mancubus]

You are welcome.

Range(Range("H8"), Range("H8").End(xlDown))

Range("H8").Resize(Application.CountA(Columns(8)))

Range(Range("H8"), Range("H8").Offset(Application.CountA(Columns("H")) - 1))

[El_Diabolo]

Even more neat solutions. Thank you so much.

[mancubus]

welcome again.

yes. there a number of ways to achieve this.

pls keep in mind that ways in post#4 are for contiguous ranges.

should there be blank cells in column H, then methods in post#2 will b better to use.

like:

Range(Range("H8"), Range("H" & Rows.Count).End(xlUp))

[El_Diabolo]

Thank you so much, mancubus. I hadn't thought of that. You have been a great help.

Best regards.

[mancubus]

I am glad it helped.