What is the difference between Range.Columns and Range.EntireColumn

Question

Dim r as Range
Set r = Range("C2:D3")

Dim r1 as Range, r2 as Range
Set r1 = r.EntireColumn
Set r2 = r.Columns

Won't both ranges represent the range "C:D"? What is the difference between the two?

Answer 1

No, EntireColumn represents the range "C:D", Columns represents the columns of the cells in the range. If you want to see this in action, here's a small sub that shows this. Place non-zero values in the entire range C2:D3, then place some in C5 and D5. The values in C5 and D5 won't change with Columns (range1), now substitute EntireColumn (range2) and see what happens.

Sub Test()

Dim range1 As Range
Dim range2 As Range

    Set range1 = Range("C2:D3").Columns
    Set range2 = Range("C2:D3").EntireColumn

    range1.Value = 0

End Sub

Also, Columns is indexed, so you can reference the first column like:

r.Columns(1)