如何在 VBA 中使用列/行索引作为范围

How to use column/row index as range in VBA

提问by curious

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

就像使用Cells(1, 1)而不是Range("A1")，在 VBA 中使用列/行索引作为范围的最佳方法是什么？

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

我想出了 2 个表示 Range("A:A") 的解决方案：

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

• 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:

编辑：注意到来自 Tehscript 的回复并感谢同样的回复。我已经试过了，但它给出了以下错误：

Run-time error '13': Type mismatch.

运行时错误“13”：类型不匹配。

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:

虽然如果第 3 行它工作正常：

Set r1 = Columns(1)

is changed to:

改为：

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

回答by Tehscript

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():

没有最好的方法来做到这一点，但有一些方法可以根据您的需要使用。例如，如果您想遍历行和列，则最好使用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)).

另一方面，您可以Range("A1:B3")根据需要以任何一种方式引用一个范围。如果您只需要参考，则应使用Range("A1:B3"). 如果您需要处理行和列，则最好使用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))

• 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:

编辑：您正在遍历 A 列中的单元格，在这种情况下，您需要使用：

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

• Columns("A").Cells或者 Columns(1).Cells