Traversing `Cells` in a `Range`

Question

Short version of the question:

The code here

Dim rng As Range
Set rng = Selection
Set rng = rng.Columns(1)
For Each cl In rng
  cl.Select    ' <-- Break #2

gives me this in the immediate window when the selection is A1:B37

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1:$A$37

Anyone can help me understanding why cl -> A1:A37 instead of cl -> A1? Note that I imagine rewriting code to get the intended results. But I would like to know what is the problem, and probably learn something new along. This is what the question is about.

---

Long version of the question (as originally posted):

I have a subroutine, which works on the selected (rectangular) range rng. The code of relevance here is shown below. It branches depending on the number ncols of columns of rng.

When ncols=1, it loops through each cell cl in rng, selecting cl and performing some actions. When the starting selection is A1:A37, this works ok, as shown by the output in the immediate window right after entering the loop at Break #1 (see code below)

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1

When ncols<>1, I want to loop through each cell cl in the first column of rng, doing the same as before. Now when the starting selection is A1:B37, this does not work, as shown by the output in the immediate window at Break #2

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1:$A$37

Anyone can help me understanding why here cl -> A1:A37 instead of cl -> A1 (as in Break #1)? Note that I imagine rewriting code to get the intended results. But I would like to know what is the problem, and probably learn something new along. This is what the question is about.

Dim rng As Range
Set rng = Selection
Dim ncols As Long
ncols = rng.Columns.Count
Dim cl As Range
' 1- If only one column is selected, ...
If (ncols = 1) Then
  For Each cl In rng
    cl.Select    ' <-- Break #1
    ...
  Next cl
' 2- If more than one column is selected, ...
Else
  Set rng = rng.Columns(1)
  For Each cl In rng
    cl.Select    ' <-- Break #2
    Dim rng2 As Range
    Set rng2 = Range(cl, cl.Offset(0, ncols - 1))
    rng2.Select
    ...
  Next cl
End If

Answer 1

I have not had a chance to test your code yet but you may simply be suffering from lack of explicity: cl is a Range, so is a Column and a Row and an Area and any other sort of range-type object. You can use a range iterator like cl : For each cl in Rng.Rows or ...in rng.Columns, or in ...rng.Cells, etc.

In other words, while you may be expecting cl to be a cell range, that may not be the case unless you make it explicit, like:

For each cl in rng.Cells

Or, since you are defining it as a single-column, this would be equivalent:

For Each cl in rng.Rows

(technically, cl represents a row range in that rng, but since it's a single column range, each "row" is a single cell, too).

Your code can acutally be quite streamlined:

Sub f()

    Dim rng As Range
    Dim cl As Range
    Dim rng2 As Range

    Set rng = Range(Selection.Address).Resize(, 1)
    ncols = Range(Selection.Address).Columns.Count

    For Each cl In rng.Cells
      cl.Select    ' <-- Break #2

      If nCols > 1 Then
          Set rng2 = Range(cl, cl.Offset(0, ncols - 1))
          rng2.Select
          '...
      End If
    Next cl

End Sub