Loop From Left to Right (Regardless of Order of Selection)

[Excel Planet]

Hi there,

I don't seem to find a solution for this challenge. Below is a dummy data for demonstration purposes only (data highlighted in blue represents cells being selected by a user):

> columns: A D H

Row 8 2 10 5

Row 18 7 44 3

Row 27 9 66

User is selecting multiple contiguous & non-contiguous cells in different columns, order of selection might not be from left to right (A to C in this case). The thing is I would like to construct a loop that would force VBA to loop first through cells in column A, column B and then C, regardless of the order in which cells were selected.

In addition, I would like to count cells in each column individually to carry-out some comparisons with another file.

I have tried below loop, however, it's looping through cells in order of selection. I searched for a method for counting cells of selection per column using "Area collection" but came up short.

Dim rng As Range

for each rng in selection

msgbox rng.address

next

Any help would be highly appreciated.

 

[JoeMo]

Here's a routine you can use to see the order of readout Excel follows for any selection, including selections of non-contiguous cells. As far as I have tested this, the areas are read in the order they were selected, but the cells within an area are read from left to right or top to bottom no matter how they were selected.

Sub ReadoutOrder()
Dim cel As Range, Ar As Range
For Each Ar In Selection.Areas
    MsgBox Ar.Address
    For Each cel In Ar
        MsgBox cel.Address
    Next cel
Next Ar
End Sub

 

[Excel Planet]

Thanks Joe for your valuable feedback.

I've tried your solution. However, it's looping through cells by order of selection. In addition, both loops return the same result, that's cell address.

I have noticed the following, "area selection" is miss leading when there are more than one cell being selected in a single column. I'm not quite familiar with this collection, however, this is at least a finding based on the code you have provided.

 

[JoeMo]

For me, the cells are read out from left to right or top to bottom within a single area, regardless of the order of selection provided contiguous cells were selected by dragging the mouse. If you select contiguous cells, one at a time, by holding down the ctrl key, then the readout follows the order of selection.

 

[Excel Planet]

For me, the cells are read out from left to right or top to bottom within a single area, regardless of the order of selection provided contiguous cells were selected by dragging the mouse. If you select contiguous cells, one at a time, by holding down the ctrl key, then the readout follows the order of selection.

It does read from left to right, however, it's not looping column by column (cells in column 1, cells in column 2, etc). I intentionally selected non-contiguous cells in multiple columns to check the loop, and it didn't loop by column.

If we accomplished the above loop, I would like to count number of cells in each column.