How to use column/row index as range in VBA

Question

Like using Cells(1, 1) instead of Range("A1"), what's the best way to use column/Row index as range in VBA?

I came up with 2 solutions to denote Range("A:A"):

• Range(Cells(1, 1), Cells(Columns(1).Rows.count, 1))
• Union(Columns(1), Columns(1))

Is there a better and more concise solution?

Edit: noted response from Tehscript and thanks for the same. I already tried that but it's giving below error:

Run-time error '13': Type mismatch.

Here's the 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

Although it works fine if the 3rd line:

Set r1 = Columns(1)

is changed to:

Set r1 = Union(Columns(1), Columns(1))

Solution

There is no best way to do this, but there are ways that you can use according to your needs. For example if you want to loop through both rows and columns you should better use Cells():

Sub RowTimesColumn()
Dim i As Long, j As Long
For i = 1 To 10
    For j = 1 To 5
        Cells(i, j) = i * j
    Next j
Next i
End Sub

On the other hand you can reference a range like Range("A1:B3") in either way depending on your needs. If you simply need the reference, you should use Range("A1:B3"). If you need to play with the rows and columns, you should better use Range(Cells(1, 1), Cells(3, 2)).

It is all about readability and functionality.

For your question, you might want to use the following:

• Range("A:A") --> Columns(1)

• Range("A:C") --> Range(Columns(1), Columns(3))

Edit: You are looping through the cells within Column A, in that case you need to use:

• Columns("A").Cells or Columns(1).Cells

Comments

[Tehscript]

If you had posted your code earlier, it would be easier to get a proper answer. What you are looking for is .Cells

[curious]

perfect! thanx again