0

While answering a question I discovered that a For Each loop using Columns() will return the same range.

Take:

    Dim colrng As Range
    
    Set colrng = Columns(1)
    
    Dim rng As Range
    
    For Each rng In colrng
        Debug.Print rng.Address, colrng.Address
    Next rng

It will return Range("A:A") for the value rng.address

If you run this, using Range("A:A") rng will be "A1", "A2", ... as expected.

    Dim colrng As Range
    
    Set colrng = Range("A:A")
    
    Dim rng As Range
    
    For Each rng In colrng
        Debug.Print rng.Address, colrng.Address
        If rng.Row = 5 Then 'No need to continue 
            Exit For
        End If
    Next rng

The same happens for Rows()

Anyone know what is happening behind the scenes here?

Warcupine
  • 4,460
  • 3
  • 15
  • 24
  • Add `.Cells`: `For Each rng in colrng.Cells`. Looping over columns means you're looping column-by-column, not over the cells in each column. Same for rows. Probably a dupe somehwere. – BigBen Sep 29 '21 at 15:41
  • @BigBen Well, that's an easy one. – Warcupine Sep 29 '21 at 15:43
  • 1
    [Good answer that address the issue](https://stackoverflow.com/a/51097214/9245853). Plus another [here](https://stackoverflow.com/a/56603480/9245853). – BigBen Sep 29 '21 at 15:49

0 Answers0