VBA Differences

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
966
if r =("A1:M1") then
I know what this would do
For each c in r

But what would be the difference between the following below:

For each c in r.rows
For each c in r.cells
For each c in r.columns
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
Use the code below to explore the differences. Basically, Each c in r and Each c in r.Cells are identical. Each c in r.rows and Each c in r.columns will typically generate errors, except for the unique cases where r is either a single row or a single column. In those cases, For each c in r.columns is identical to For Each c in r, and For each c in r.rows is identical to For each c in r, respectively.
Code:
Sub try()
Dim r As Range, c As Range
Set r = Range("A1:M1")
For Each c In r   'substitute r.rows, r.cells and r.columns for r
    MsgBox c.Value
Next c
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
if r =("A1:M1") then
I know what this would do
For each c in r

But what would be the difference between the following below:

For each c in r.cells --- For each cell in the range.
For each c in r.rows --- For each row in the range.
For each c in r.columns --- For each column in the range.
Examples:

Code:
Sub [COLOR=#0000ff]test_cell[/COLOR]()
  Dim r As Range, c As Range
  Set r = Range("A1:D5")
  For Each c In [COLOR=#0000ff]r.Cells[/COLOR] 'or r
      MsgBox c.[COLOR=#0000ff]Address[/COLOR]
  Next
End Sub


Sub [COLOR=#008000]test_row[/COLOR]()
  Dim r As Range, c As Range
  Set r = Range("A1:D5")
  For Each c In [COLOR=#008000]r.Rows[/COLOR]
      MsgBox c.[COLOR=#008000]Row[/COLOR]
  Next
End Sub


Sub [COLOR=#ff0000]test_column[/COLOR]()
  Dim r As Range, c As Range
  Set r = Range("A1:D5")
  For Each c In [COLOR=#ff0000]r.Columns[/COLOR]
      MsgBox c.[COLOR=#ff0000]Column[/COLOR]
  Next
End Sub
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top