Help with Resize function to select non-adjacent columns

[Marbleking]

Hi,

I am trying to modify a VBA code in order to get the output from the code pasted in columns N, R and V instead of the original, and adjacent, columns N, O and P in a worksheet named “ows”.

There are a few lines in the code that I need help to modify:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I am quite inexperienced with VBA and tried the modifications below, but got Syntax Error:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any suggestions?

Regards,
Marbleking

[BrianM45]

Hi Marbleking,

the correct syntax would be

Please Login or Register  to view this content.

But, to my knowledge, it is impossible on discontinuous range

What works is

Please Login or Register  to view this content.

Cordialy

[Marbleking]

Thanks, BrianM45.

I’ll test your suggestion as soon as I have the opportunity. It looks like the latter piece of code would affect the columns inbetween N, R and V, though.

Perhaps there exists an alternative to using the Resize function that would work on discontinuous ranges.

Regards

[BrianM45]

Hi,

Can you tell us why not to use :

Please Login or Register  to view this content.

Regards

[Marbleking]

Hi,

I can probably use that when the equality is "", but not for these two lines:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards

[Marbleking]

Hi,

I found out I could use the Union function, like this:

Please Login or Register  to view this content.

Regards

[Rick Rothstein]

I don't think what you posted will work correctly. What exactly is arr3 ?

[Marbleking]

You may be right. It seemed to identify the desired ranges, but I got type mismatch errors when I ran the code. Please have a look at this thread for the complete code:

"Make VBR independent of column order in data table, output results in non-adjacent columns"

Regards,
Marbleking

[Rick Rothstein]

You did not answer my question (which is not addressed in your posted code)... what exactly is arr3 (describe it in detail)?

[Marbleking]

arr3 is a variable in the last of several steps for organising output of text data in a worksheet. I am not able to describe it in more detail. I wrote formulas with Excel functions using a lot of helper columns at first, but got that logic "translated" to VBA by someone else. The complete code is included in the thread "Make VBR independent of column order in data table, output results in non-adjacent columns" along with some broader questions, if you'd have a look.

Regards,
Marbleking

[Rick Rothstein]

So your arr, arr3 and so forth are each single text strings and not arrays then (most people tend to use arr for arrays)? Meaning you are trying to put the same text value in multiple cells?

[Marbleking]

These may be arrays as you say, I am a bit on shaky ground when it comes to VBA, but yes, a lot of the text values are put in multiple cells. The code organises hierarchical data consisting of three levels, and there are a one to many relationship between each of the two first levels and the succeeding level.

[Marbleking]

Hi,

I have attached a workbook that includes the code and some data in this thread, so it is easier to see how it works:

"Make VBR independent of column order in data table, output results in non-adjacent columns"