Using cell value as a variable for column value

[ChristineJ]

This excerpt of code works to define two ranges of columns.

Call SplitAny(Range("D:R,FZ:GK"), Range("68:205,542:795"), Range("D68"), Range("FZ566"))

This excerpt of code works to replace the "D" with the value in cell ED33.

Call SplitAny(Range(Range("ED33").Value & ":Z,FZ:GK"), Range("68:205"), Range("D68"), Range("FZ566"))

I also now want to replace the "R", "FZ", and "GK" in the first line of code with the cell values in ED34, ED35, and ED36, respectively.

I am trying the following, but getting syntax errors.

Call SplitAny(Range(Range("ED33").Value:Range("ED34").Value,Range("ED35").Value:Range("ED36").Value), Range("68:205"), Range("D68"), Range("FZ566"))

How do I need to tweak this? Thanks.

 

[p45cal]

try:

Call SplitAny(Range(Range("ED33").Value & ":" & Range("ED34").Value & "," & Range("ED35").Value & ":" & Range("ED36").Value & ""), Range("68:205"), Range("D68"), Range("FZ566"))

(Bear in mind that your second code snippet:

Call SplitAny(Range([COLOR=#ff0000]Range("ED33").Value [/COLOR]& ":Z,FZ:GK"), Range("68:205"), Range("D68"), Range("FZ566"))

is not equivalent to your first:

Call SplitAny(Range("[COLOR=#ff0000]D:R,FZ:GK[/COLOR]"), Range("68:205[B][COLOR=#0000ff],542:795[/COLOR][/B]"), Range("D68"), Range("FZ566"))

since it misses out the blue highlighted bit.)

 

[ChristineJ]

Wonderful! It works perfectly.

I was playing with the row ranges as well and did not post identical versions of that part. But that is all OK now.

Appreciate the quick response. This really helps!

 

[Norie]

Christine

Out of curiousity, what does the SplitAny function/sub do?

 

[ChristineJ]

It is part of code that splits the screen into two screens that both have scrollbars and hides all of the cells where I have content. The SplitAny sub then unhides the cells that are to appear in the left and the right screen and properly positions them.