title: How to use column/row index as range in VBA
body: <p>Like using <code>Cells(1, 1)</code> instead of <code>Range("A1")</code>, what's the best way to use column/Row index as range in VBA?</p>
  
  <p>I came up with 2 solutions to denote Range("A:A"):</p>
  
  <ul>
  <li><code>Range(Cells(1, 1), Cells(Columns(1).Rows.count, 1))</code></li>
  <li><code>Union(Columns(1), Columns(1))</code></li>
  </ul>
  
  <p>Is there a better and more concise solution?</p>
  
  <p><strong>Edit:</strong> noted response from Tehscript and thanks for the same. I already tried that but it's giving below error:</p>
  
  <blockquote>
    <p>Run-time error '13': Type mismatch.</p>
  </blockquote>
  
  <p>Here's the code:</p>
  
  <pre><code>Sub tfind()
      Dim r1 As Range
      Set r1 = Columns(1)
      MsgBox mCount("Test:", r1)
  End Sub
  Function mCount(find As String, lookin As Range) As Long
     Dim cell As Range
     For Each cell In lookin
         If (Left(cell.Value, Len(find)) = find) Then mCount = mCount + 1
     Next
  End Function
  </code></pre>
  
  <p>Although it works fine if the 3rd line:</p>
  
  <pre><code>Set r1 = Columns(1)
  </code></pre>
  
  <p>is changed to:</p>
  
  <pre><code>Set r1 = Union(Columns(1), Columns(1))
  </code></pre>