PDA

View Full Version : [SOLVED] Quotes within quotes format problem



El_Diabolo
11-20-2013, 11:32 AM
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
11-20-2013, 11:44 AM
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
11-20-2013, 12:30 PM
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
11-20-2013, 12:44 PM
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
11-20-2013, 01:11 PM
Even more neat solutions. Thank you so much.

mancubus
11-20-2013, 02:09 PM
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
11-20-2013, 05:39 PM
Thank you so much, mancubus. I hadn't thought of that. You have been a great help.

Best regards.

mancubus
11-20-2013, 10:55 PM
I am glad it helped.