vba 如何选择表格行/完整表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10183980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select table rows/complete table?
提问by SpeedCrazy
The setup: I have an excel doc with a form for entering data, the data for that form is entered in a table to allow for easy entering of multiple rows of data. At least i thought so.
设置：我有一个带有用于输入数据的表单的 excel 文档，该表单的数据被输入到一个表中，以便于输入多行数据。至少我是这么认为的。
So right now i am trying to select the table to insert its data in the appropriate places. My question, i think, is this: do i select one table row at a time, or the whole table and deal with each row separately. And how do i do that?
所以现在我正在尝试选择表以在适当的位置插入其数据。我想，我的问题是：我是一次选择一个表格行，还是整个表格并分别处理每一行。我该怎么做？
I tried Sheets("Form").Range("dataForm[#ALL]").Selectand several variations thereof and none worked.
我尝试Sheets("Form").Range("dataForm[#ALL]").Select了其中的几种变体，但都没有奏效。
If i select the table as a whole i need to be able to deal with each row seperately and if i select each row individually i need to be able to be able to start at the top of the table as the data must be in order.
如果我选择整个表格，我需要能够单独处理每一行，如果我单独选择每一行，我需要能够从表格的顶部开始，因为数据必须是有序的。
Any ideas?
有任何想法吗？
EDit: To add detail. I have a form as stated above and its data must be inserted in different tables dependent on the value of certain cells in the form. For ease of discussion we will name that cell type, it has three possible values, as defined in a dropdown. Those values are income, expense and transfer. Based on those values we decide which table to add the data to. Income to the income table expense to the expense, etc.
编辑：添加细节。我有一个如上所述的表格，它的数据必须插入到不同的表格中，具体取决于表格中某些单元格的值。为便于讨论，我们将命名该单元格类型，它具有三个可能的值，如下拉列表中所定义。这些价值是收入、费用和转移。根据这些值，我们决定将数据添加到哪个表。收入到收入表费用到费用等。
So what i am trying to do is select as many rows as there are and insert each one into the correct table. The sorting is slightly more complicated than i have explained but if i can figure out the initial sort then it should be simple to sort it a few more times.
所以我想要做的是选择尽可能多的行并将每一行插入到正确的表中。排序比我解释的稍微复杂一些，但如果我能弄清楚初始排序，那么再排序几次应该很简单。
回答by Doug Glancy
This should help answer your questions.
这应该有助于回答您的问题。
Sub TableStuff()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow
Dim i As Long
Set lo = ActiveSheet.ListObjects(1)
With lo
'this is the address of the whole table
Debug.Print .Range.Address
For i = 1 To 10
Set loRow = .ListRows.Add(i)
loRow.Range.Cells(1).Value = "test" & i
Next i
Debug.Print .Range.Address
'address of data rows
Debug.Print .DataBodyRange.Address
End With
End Sub
I have two posts on my blog about tables. A recent onemight also provide some insights.
我的博客上有两篇关于表格的帖子。一个最近的一个也可能提供一些见解。
EDIT: Based on comments below and edit to OP:
编辑：基于下面的评论并编辑到 OP：
This assumes two tables on Activesheet, tblSource and tblIncome. It filters the source table to Income, copies copies the visible rows and inserts them at the end of tblIncome. Finally, it deletes the source rows (all but one).
这假设 Activesheet 上有两个表，tblSource 和 tblIncome。它将源表过滤为 Income，复制可见行并将它们插入到 tblIncome 的末尾。最后，它删除源行（除一个之外的所有行）。
You'll want to add a loop to have it work for the other two categories:
您需要添加一个循环以使其适用于其他两个类别：
Sub MoveTableStuff()
Dim loSource As Excel.ListObject
Dim loTarget As Excel.ListObject
Dim SourceDataRowsCount As Long
Dim TargetDataRowsCount As Long
Set loSource = ActiveSheet.ListObjects("tblSource")
Set loTarget = ActiveSheet.ListObjects("tblIncome")
With loSource
.Range.AutoFilter Field:=1, Criteria1:="income"
SourceDataRowsCount = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
End With
With loTarget
TargetDataRowsCount = .DataBodyRange.Rows.Count
.Resize .Range.Resize(.Range.Rows.Count + SourceDataRowsCount, .Range.Columns.Count)
loSource.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
.DataBodyRange.Cells(TargetDataRowsCount + 1, 1).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End With
With loSource
.Range.AutoFilter
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
End With
End Sub
回答by Alpha
If you already gave a name to your table, I have a function to get its full data range:
如果你已经给你的表命名了，我有一个函数来获取它的完整数据范围：
Public Function GetTableByName(ByVal ws As Worksheet, ByVal tbName As String) As Range
Dim lObj As ListObject
For Each lObj In ws.ListObjects
If Trim(UCase(lObj.Name)) = Trim(UCase(tbName)) Then
Set GetTableByName = lObj.DataBodyRange
Exit Function
End If
Next lObj
End Function
