Dim myCollection: As Collection vs As Variant-CodePudding

The first subroutine below doesn't work, but the second does. Why?

The only differente between the two is the type of the rs variable (collection vs variant).

The first subroutine gives me the following error:

"Run-time '13': Type mismatch"

Sub It_Doesnt_Work()
    Dim rg As Excel.Range, rs As Collection
    Set rg = Application.Range("myRange")
    Set rs = rg.CurrentRegion.Rows
    Debug.Print rs.Count
End Sub

Sub It_Works()
    Dim rg As Excel.Range, rs As Variant
    Set rg = Application.Range("myRange")
    Set rs = rg.CurrentRegion.Rows
    Debug.Print rs.Count
End Sub

CodePudding user response：

This rg.CurrentRegion.Rows is of type Range and you try to push it into rs As Collection which is a collection. Since Excel has no implicit conversion for ranges to collections you cannot do that.

Either you write a function that explicitly converts those types, or you need to define your variabe rs As Range or Variant as you did, but Range would be better.