excel表中第一个单元格的位置

I use Excel tables a lot. It makes it possible to have multiple tables in one worksheet. But I have got into a problem in VBA which I do not know how to solve.

我经常使用Excel表。它使得在一个工作表中拥有多个表成为可能。但是我在VBA遇到了一个我不知道如何解决的问题。

Let us say that I have a table called "tbl" in my spreadsheet. It contains the following data:

我们假设我的电子表格中有一个名为“tbl”的表。它包含以下数据:

I have the following code which works:

我有以下代码可行:

Sub test()
    Dim rng As Range
    Dim arr() As Variant
    Set rng = ActiveSheet.ListObjects("tbl").DataBodyRange
    arr = rng.Value
End Sub

This code will put the whole table into arr. But there are situations where I only want some rows in the table. Let us say that I want row 4 and 5 i.e:

这段代码将整个表放入arr。但有些情况下我只想要表中的某些行。让我们说我想要第4行和第5行,即:

2  3000
2  4000

The following code will do the trick:

以下代码将起到作用:

arr = Range("A4:B5")

But there are cases where I have many tables located in different places in my spreadsheet. Therefore I need to work out where the first cell in the table is located (upper left). If it is located in K1, I need to get both the column and the row (K and 1).

但有些情况下,我的电子表格中有许多位于不同位置的表格。因此,我需要找出表中第一个单元格所在的位置(左上角)。如果它位于K1,我需要同时获得列和行(K和1)。

Is there an easy way to do this?

是否有捷径可寻?

3 个解决方案

#1

Use Cells():

Sub test()
Dim rng As Range
Dim arr() As Variant
Set rng = ActiveSheet.ListObjects("tbl").DataBodyRange
arr = ActiveSheet.Range(rng.Cells(3, 1), rng.Cells(4, 2)).Value    
End Sub

#2

If you just want the third and fourth data rows (i.e. fourth and fifth rows if you count the heading row), you could use

如果您只想要第三和第四行数据(如果计算标题行,则为第四行和第五行),您可以使用

Sub test()
    Dim rng As Range
    Dim arr() As Variant
    Set rng = ActiveSheet.ListObjects("tbl").DataBodyRange
    arr = rng.Rows("3:4").Value
    'or
    arr = rng.Range("A3:B4").Value
End Sub

Because the Rows and Range properties are being applied to the rng object, the references ("3:4" and "A3:B4") are relative to the start of the rng object.

由于Rows和Range属性应用于rng对象,因此引用(“3:4”和“A3:B4”)相对于rng对象的开头。

So you could also get the worksheet address of the first cell in rng by using rng.Range("A1").Address (or rng.Cells(1, 1).Address), or you can get the first cell's worksheet row and column by just using rng.Row and rng.Column respectively (they both default to returning the value for the first cell).

所以你也可以使用rng.Range(“A1”)。地址(或rng.Cells(1,1).Address)获取rng中第一个单元格的工作表地址,或者你可以得到第一个单元格的工作表行和只使用rng.Row和rng.Column列(它们都默认返回第一个单元格的值)。

#3

The first cell in a table has some unique properties:

表中的第一个单元格具有一些独特的属性:

-It is not empty

- 它不是空的

-The cell above it is null

- 它上面的单元格为空

-The cell to the left of it is null

- 它左边的单元格为空

-The cell below it is not null

- 它下面的单元格不为空

Using these properties you can do something like:

使用这些属性,您可以执行以下操作:

1) Loop over every cell

1)循环每个细胞

2) If that cell has the above properties, then it is a "top left" cell

2)如果该单元格具有上述属性,则它是“左上角”单元格

3) Do whatever with that table

3)对那张桌子做任何事情

#1