How do you count the number of rows when you have multiple non-continues selections

I have put together this bit of code to help speed up our electrical interconnect reports and it works great except that it only works on a single selection of several continues rows (click row 2, Shift+ Click row 5). If I attempt to use Ctrl+Click and select several different rows the code falls apart.

Ideally I would like to be able to make any number of Shift+Click and Ctrl+Click Selections and then run my code.

I think I only need to know the number of rows selected.

the purpose of this code is to flip the rows selected by the user so that what ever data was on the right hand would be moved to the left hand. The attached picture shows that some of the data that I am working with.
Capture.PNG

All of the data is flipped about column I. The data to be flipped is located between columns C & O, this range has also been formatted as text.

Can anyone help? I have been trying but can't find the answer.

Thanks in advance.

Please Login or Register  to view this content.

xladept,

I removed the .value, but it seems to be performing the same as it did before.
What was the purpose of removing the .value?

If I Ctrl+Click row 2 and Ctrl+Click row 4 it still does not flip the data.

That makes sense.
Sorry for being inappropriate.

What kind of selection did you do?
If I Ctrl+Click row 2 & Ctrl+Click row 3 then run the code, it works fine.

If I Ctrl+Click row 2 & Ctrl+Click row 4 then run the code, it does not work.

If I Click row 2 & Shift+Click row 6 & Ctrl+Click row 7 then run the code, it works fine.

If I Click row 2 & Shift+Click row 6 & Ctrl+Click row 8 then run the code, it does not work.

The problem seems to be happening here.

Please Login or Register  to view this content.

If I step through the code and look at the details of this array, it is only showing 5 rows of data even though I really selected 6 rows. (Click row 2 & Shift+Click row 6 & Ctrl+Click row 8)

I suppose that this might be helpful.
WIREFRM2.xls

The Code should work for any number of within a single selection set; ie. Click row 2 Shift+Click row X, or Click & Hold row 2 drag down to row X and release.
The Click & drag selection looks the same as a shift selection.
Click and Drag Selection.PNG

It also seems to work if the user uses Ctrl+Click row 2 & Ctrl+Click row 3 & Ctrl+Click row 4... this also seems to work fine.
Ctrl+Click selection.PNG

If the user skips a row during the Ctrl+Click selection, as shown below. the data in the last row (7) will be trashed.
Ctrl+Click selection and skipped row.PNG
This will also happen if rows 2-5 are selected with a Click Drag or Click & Shift+Click selections.

Hopefully I am making sense.

So none of the data is trashed when you select row 2 and row 4?
That would be a first for me. I have never had a situation where I couldn't run my own code but someone else could.
I don't think this would cause any problems but I am running this code from a ribbon command. although when I originally created it I was running it right out of VBA, and I have had the same problem this whole time.

I just found something that I think I can use to solve my problem, but I don't know how to use it.
If you set the selection as an object and then look at the details of that object in the Locals window, you will find that the object has Areas. Under the Areas you find item1, item2... each item represent a selection area. for example row 2 would be item1 and row 4 would be item2.
Under the item (1 or 2) there is value2. under value2 there is value2(#), where # represents the number of rows in that selection group. if you expand value2(1) you will find the individual cell values for the first row of that selection.

So I think if I set the selection as an object and then starting with the first selection set (item1), move those values into the array and then do the same thing on the next selection set in the object.
problem is I have no idea how to extract data that is imbedded that deep.
I think it would be something like; object.Areas.Item1.Value2.Value2(1),Value2(1,1).value would return the value of the first cell in the first row of the first selection set.

IT WORKS!!!!!!!!

Here is the finished code.

Please Login or Register  to view this content.

Thank you Izandol & xladept! I really appreciate the help you provided!