使用表和循环的 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15221733/
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
VBA Working with Tables and Loops
提问by william-1066
Create a VBA loop to re-size all of the tables in an Excel project.
创建一个 VBA 循环来重新调整 Excel 项目中所有表的大小。
A list of table names is stored in the "Tlist" named range
表名列表存储在“Tlist”命名范围中
This code all works if the table names are written in.
如果写入了表名，则此代码均有效。
The code works in this loop for the column count, but on the second reference of x , the re-size, I get an error of 'subscript out of range'
该代码在此循环中用于列数，但在 x 的第二个引用中，重新调整大小时，我收到“下标超出范围”的错误
Hovering over the second x on debug, Excel seems to read it in right, but I can't get it to re-size the table Something to do with the list object function not supporting the text in the loop? Or am I doing this loop wrong and need to define x differently? Any help greatly appreciated.
在调试时将鼠标悬停在第二个 x 上，Excel 似乎正确读取它，但我无法让它重新调整表格大小 与不支持循环中的文本的列表对象函数有关吗？或者我做这个循环错了，需要以不同的方式定义 x ？非常感谢任何帮助。
Sub RSizeTables()
Dim rr As Integer
Dim cc As Integer
Dim x As Range
Dim ￡Table As Range
Set ￡Table = Range("Tlist")
For Each x In ￡Table
rr = 2
cc = Range(x).Columns.Count
With Sheets("Data").ListObjects(x)
.Resize .Range.Resize(rr, cc)
End With
Next x
End Sub
回答by Peter Albert
This code will resize all tables to two rows:
此代码将所有表调整为两行：
Sub ResizeAll()
Dim ws As Worksheet
Dim lo As ListObject
'If you only have tables in one sheet, use this
Set ws = Sheets("Data")
'Else this:
'For Each ws in Sheets
For Each lo In ws.ListObjects
lo.Resize lo.Range.Resize(2)
Next lo
'Next ws
End Sub
If you need to loop only the tables in TList, this will do the job:
如果您只需要循环 中的表TList，这将完成这项工作：
Sub ResizeTList()
Dim varTableName As Variant
For Each varTableName In Range("TList")
With Sheets("Data").ListObjects(varTableName)
.Resize .Range.Resize(2)
End With
Next varTableName
End Sub
