0

I have a multi-sheet workbook I'm looping through to pull out key data ranges into an array. A couple of the sheets don't follow the standard format and so instead of simply grabbing Range("D6:G100"), I need to piece it together from other columns, and re-order them (just in code, not modifying the sheet).

Tried

Set rTaskRange = Union(Range("D6:D100"), Range("H6:H100"), Range("I6:I100"), Range("G6:G100"))

But in the immediate window, I can see VBA is reverting the column order back to original. And also lying to me because columns E and F seem to be included even though it says they're not.

?rTaskRange.address
$D$6:$D$100,$G$6:$I$100

So the question is: how to build a range out of 4 non-contiguous columns, and also re-arrange them so that (in this example) I is the 3rd column in the range?

Improve this question
6
  • 1
    Seems like an XY problem. If you're pulling non-contiguous ranges into an array, you'll need to loop. So just loop in the order you want. Commented Sep 15, 2023 at 14:36
  • "I have a multi-sheet workbook ..." - the code you are showing doesn't explicitly say what sheet the Range()s should be on. You need to add that qualifier, otherwise it's going to use the Range() on whatever the ActiveSheet is. ...e.g. ...Union(Worksheets("Sheet10").Range("D6:D100")... Commented Sep 15, 2023 at 14:37
  • all seem to be correct - it unites the range G:I and separately shows D -see the link vremya-ne-zhdet.ru/vba-excel/metod-application-union/… so rhe result is the array of ranges 'cos the ranges don't touch Commented Sep 15, 2023 at 14:46
  • 1
    "columns E and F seem to be included even though it says they're not" - what makes you think that though? Commented Sep 15, 2023 at 15:26
  • 2
    ...note that because rTaskRange is a discontinuous/multi-area range, you can't loop over it using something like For i=1 to rTaskRange.Columns.Count: Debug.Print rTaskRange.Columns(i).Address: Next i - that will start with the first area but then just continue from there, ignoring the other areas in the range. I'm guessing that's why you think columns E and F are included? Commented Sep 15, 2023 at 16:05
Related questions
3069
Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3?
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
651
How can I avoid using Select in Excel VBA?
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.