通过 Excel VBA 将数据插入表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43303121/
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
Inserting data into table via Excel VBA
提问by Adam
I have an excel table and I am trying to automate the deletion of data and insertion of data into an excel table.
我有一个 excel 表，我正在尝试自动删除数据并将数据插入到 excel 表中。
I am using this VBA code to remove my data from my table
我正在使用此 VBA 代码从我的表中删除我的数据
Sub Macro2()
Application.ScreenUpdating = False
Sheets("Report").Select
ActiveSheet.ListObjects("Report").HeaderRowRange.Select
'Remove the filters if one exists.
If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If
With Worksheets("Report").ListObjects("Engagement_report")
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub
However, when i try to insert data(about 11 000 rows) into the same table it is very slow and my excel crashes. I am not sure if its because the resize function i does deletes all rows and hence when i insert data again, it is redoing the range and hence its slow.
但是，当我尝试将数据（大约 11 000 行）插入同一个表时，它非常慢并且我的 excel 崩溃了。我不确定是不是因为我的调整大小函数删除了所有行，因此当我再次插入数据时，它正在重做范围，因此它很慢。
Does anyone have any suggestions to help in this?
有没有人有任何建议可以帮助解决这个问题？
Appreciate your help
感谢你的帮助
采纳答案by Rajesh S
Public Sub addDataToTable(ByVal strTableName As String, _
ByVal strData As String, ByVal col As Integer)
ActiveSheet.Table(strTableName).Select
If strTableName.Rows.Count = 1 Then
strTableName(Row, col).Value = strData
Else
strTable(LastRow, col).Value = strData
End If
End Sub
NB : I think this will be helpful to you.
注意：我认为这对你有帮助。
回答by Rajesh S
This sample code will help you to insert multiple rows.
此示例代码将帮助您插入多行。
Sub InsrtRw2()
Dim c As Range, fst As Range, c2 As Range
Set c = Cells.Find("Test", MatchCase:=False, _
lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
c(2).EntireRow.Resize(10).Insert
Set fst = c
again:
Set c2 = Cells.FindNext(c)
If Not c2 Is Nothing Then
If c2.Address <> c.Address _
And c2.Address <> fst.Address Then
c2(2).EntireRow.Resize(10).Insert
Set c = c2
GoTo again
End If
End If
End If
End Sub
