non-contiguous range issue

[reneuend]

I'm building a range using Excel VBA that needs to skip one column. The rest are contiguous.

The range is dynamic, but in my test run it equals "F13:M13,O13:O13".
When I look at the values in this range it is only showing the values for F13:M13. Why doesn't it show O13:O13?

sRange = sCol1 & sRow & ":" & sCol2 & sRow & "," & sCol3 & sRow & ":" & sCol3 & sRow
            
Set InputRngB = ActiveWorkbook.Worksheets(TEST_TAB).Range(sRange)

Thanks for replying!

 

[shg]

When I look at the values in this range it is only showing the values for F13:M13

Look at them in what way?

 

[reneuend]

Thanks for replying shg4421.

I'm looking at it during a debug run from the WATCH pane. I also copied it to a location on the worksheet and it is only copying the values before the comma.

I noticed that if I use InputRngB.Select that it correctly selects the cells. It's just not getting me the last value.

 

[shg]

The range contains all the cells. Certain functions, and certain UI actions, only look at the first area of a multi-area (including copy) range.

That's why I asked what you're trying to do.

 

[reneuend]

I'm specifically setting the range = to another range that defines a position on the spreadsheet. I still don't understand why it doesn't display all the values in the WATCH pane. If it know how to select the cells, why can't it show the values?

 

[shg]

I don't use the Watch window to look at ranges, so I couldn't say.

Assuming your code has a broader purpose than illuminating the Watch window, though, if you explain what that is, perhaps I can help.

 

[tushiroda]

not sure what you are doing either tho something like the below might be what you are looking for. not everything can handle non-contiguous ranges as well.

Set srange = Union(Range("F13:M13"), Range("O13:O13"))

 

[reneuend]

LOL.

I have an area on a spreadsheet designated for rows of input values. Depending on a selection from a dropdown, only certain columns of the input values are used. Each row is processed separately in a loop. The input values are placed into a range and copied to an "temp" range that is a separate area on the spreadsheet. This data is then processed in MathCad and the values are placed in an "output" range.

The area in question is grabbing the input data, which can be non-contiguous columns of data into a range to be copied to the temporary area. Before today, I didn't have to worry about non-contiguous ranges.

I hope that was clear enough...hard to do without pictures.

 

[reneuend]

not sure what you are doing either tho something like the below might be what you are looking for. not everything can handle non-contiguous ranges as well.

Set srange = Union(Range("F13:M13"), Range("O13:O13"))

Thanks tushiroda. I'll give it a try!

 

[dcardno]

You will have to refer to each discontinuous portion of the range through the area property of the range object. The example in the code below may make it clearer - run it with a range including a couple of columns. Excel is a bit odd about discontinuous ranges - as shown in the first example (commented out) it will select the target paste range based on the multi-area "rg" range - but it won't actually paste the area(1) and area(2) values into it in one swipe. The example2 code corrects that overlapping paste problem. Comment out one or the other to see the effect...

Sub TestRanges()

Dim rg              As Range
Dim cl              As Range
Dim i               As Integer

Set rg = Selection
Set rg = Union(rg, rg.Offset(3, 0))

i = 1
For Each cl In rg.Areas(1)
    cl.Value = i
    i = i + 1
Next cl

For Each cl In rg.Areas(2)
    cl.Value = i
    i = i + 1
Next cl

' example1 - copies to the offset to area 1 and 2
'rg.Areas(1).Copy
'rg.Offset(0, rg.Areas(1).Columns.Count + 3).PasteSpecial (xlPasteValues)
'
'rg.Areas(2).Copy
'rg.Offset(3, rg.Areas(2).Columns.Count + 3).PasteSpecial (xlPasteValues)
' end example 1

' example2 - copies to offset for each area
rg.Areas(1).Copy
rg.Areas(1).Offset(0, rg.Areas(1).Columns.Count + 3).PasteSpecial (xlPasteValues)

rg.Areas(2).Copy
rg.Areas(2).Offset(0, rg.Areas(2).Columns.Count + 3).PasteSpecial (xlPasteValues)
' end example 2

End Sub