Copying Non-Contiguous Cells and Storing

[JValenti]

Hello All,

I am currently trying to copy non-contiguous cells in excel which I know you cannot do directly, and then I need to copy these non-contiguous cells into another program, so I cannot just copy and paste in fragments.

An example of what my data looks like now is below. So now I would have to manually copy Row 2 and 3 (Pos thru Amount), paste into my program. Then I would need copy Row 4 and 5 (Pos thru Mat.No), paste into my program. If when copying Rows 4 and 5 I include the Amount column the copy errors out. The S. column is copied every time but it will always be blank.

[TABLE="width: 396"]
<tbody>[TR]
[TD]Pos[/TD]
[TD]No[/TD]
[TD]Cnt[/TD]
[TD]S.[/TD]
[TD]Mat.No[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]111111[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]11121[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]111112[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]111113[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="5"><col></colgroup>[/TABLE]


The data could include out of order values where values with an amount could be between those without, and each time this happens it cause another manual copy and paste step into a program. I cannot filter the data and it needs to go in as is.

Thanks for the help and hopefully someone can think of a solution.

 

[JLGWhiz]

Your example does not coincide with your narrative. The example shows contiguous cells, but the narrative indicates they are not contiguous. Can you clarify so we have a better understanding of how you are trying to do the copy. Posting any code you are using will be helpful.

 

[JValenti]

Hello JLGWhiz,

Yes, they intially look contiguous as Rows 2-5 are touching, and they are shown looking like a 4x6 array. However, in my comments I tried to describe what is happening is that rows 2-3 are a 2x6 array and rows 4-5 are a 2x5 array. The fact that this is no longer based on a rectangular/square selection excel looks at it as non-contiguous.

Thanks for the quick reply

 

[JLGWhiz]

Hello JLGWhiz,

Yes, they intially look contiguous as Rows 2-5 are touching, and they are shown looking like a 4x6 array. However, in my comments I tried to describe what is happening is that rows 2-3 are a 2x6 array and rows 4-5 are a 2x5 array. The fact that this is no longer based on a rectangular/square selection excel looks at it as non-contiguous.

Thanks for the quick reply

I don't understand what you are describing. If your data resides in cells A2:F5 with cells E4:F5 being blank, you should be able to copy the range A2:F5 and paste it without getting an error. Excel would simply paste cells E4:F5 as blanks. At least it does on my system. They are not non-contiguous, they are just blank. So either I am having a terminology problem or there is something lost in translation. You are correct that non-contiguous cells cannot be directly copied and pasted, but that means when you are attempting to copy only cells with data in a 6 x 5 matrilx and paste it to cells in a 5 x 4 matrix, as an example. But that does not appear to be the case with your matrix. It would be easier to understand if you used column Letters and row Numbers to illustrate your matrix, then we could see what is contiguous and what is not contiguous. Column Headers do not tell where on the sheet the data actually resides.

It may be possible to use an array or the Union method to do what you want, but I can't tell with what has been presented so far. Note the last sentence in the blurb at the bottom of my post.

 

[JValenti]

Hello JLGWhiz,

The problem is I am not copying into Excel, as I would agree I absolutely could copy A2:F5 and not receive an error the problem is that the program that I am copying into interprets the blank cell when copied over as entering data into a field that doesn't exist. So if I try copying as one block I would receive something similar to below output, with an extra line of incorrect data


[TABLE="class: cms_table, width: 396"]
<tbody>[TR]
[TD]Pos
[/TD]
[TD]No
[/TD]
[TD]Cnt
[/TD]
[TD]S.
[/TD]
[TD]Mat.No
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]111111
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]11121
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]111112
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]111113
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[JLGWhiz]

Hello JLGWhiz,

The problem is I am not copying into Excel, as I would agree I absolutely could copy A2:F5 and not receive an error the problem is that the program that I am copying into interprets the blank cell when copied over as entering data into a field that doesn't exist. So if I try copying as one block I would receive something similar to below output, with an extra line of incorrect data


[TABLE="class: cms_table, width: 396"]
<tbody>[TR]
[TD]Pos
[/TD]
[TD]No
[/TD]
[TD]Cnt
[/TD]
[TD]S.
[/TD]
[TD]Mat.No
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]111111
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]11121
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]111112
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111113
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

Then I cannot give you any constructive advice, since I have no idea what the program you are interfacing with requires in order to complete the paste action. Sorry,
regards, JLG