2

I can iterate over a range of cells to operate on each cell individually, if the range is specified by address:

Dim cell as Range
For Each cell In Range("A1:A10")
  debug.print cell.Address
Next cell

Produces the expected output:

$A$1
$A$2
... etc

But when I specify a column from the Columns collection of a range, iteration only runs once with the whole column:

For Each cell In UsedRange.Columns(1)
    Debug.Print cell.Address
Next cell

Produces just one output, with the whole column:

$A$1:$A$20

Is UsedRange.Columns(1) not a Range object? If not, what is it??

I've read this discussion of Rows/Columns as ranges and this on Range vs Range.Cells but I still can work out how to iterate over each cell in UsedRange.Columns(1).

LondonRob
  • 73,083
  • 37
  • 144
  • 201
  • `For Each cell In ActiveSheet.Columns(1)` loops the entire column for me, so I guess it's the fault of how UsedRange works. Which I'd have to research more tbh. – Christofer Weber May 04 '19 at 15:15
  • @ChristoferWeber Iterating over `ActiveSheet.Columns(1)` has exactly the same problem as I'm describing. You get one iteration, with address "$A:$A", not many iterations one for each cell in the column. So it's nothing to do with `UsedRange`. – LondonRob May 04 '19 at 15:17
  • That's odd, I used that, with `cell.Value = 1` in the loop, and it filled the entire column with 1's. But when trying `UsedRange.Columns(1).Cells` I only get `A1` – Christofer Weber May 04 '19 at 15:21
  • 1
    _Is UsedRange.Columns(1) not a Range object? If not, what is it??_ - it is a Range object, but a special one. `For Each` iterates all elements of collection. If you check `Count` property for _normal_ `$A$1:$A$20` - it is 20, but if you create same `Range` using Columns(i), the `Count` is 1. I don't know any other property to distinguish normal and row/column ranges. – BrakNicku May 04 '19 at 16:05

1 Answers1

4

That is because a Column is a distinct Object itself. If you loop over columns, you loop one column at a time. To loop over the cells in a column:

Dim cell as Range
For Each cell In UsedRange.Columns(1).Cells
  debug.print cell.Address
Next cell
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    This is a good answer, but notice that I'm not iterating over `Columns`, I'm iterating over `Columns(1)`. This still takes a bit of further explaining I'd say. It has to do with the fact that `Columns(1)` doesn't do quite what I think it's doing. – LondonRob May 04 '19 at 15:23
  • 2
    @LondonRob Actually you **ARE** iterating over columns, but the iteration is over only ONE column ! ..........................this is quite confusing................I asked the same question myself several years ago......................... – Gary's Student May 04 '19 at 15:28