如何通过VBA清除多个命名工作表中的数据? [英] How do I clear data from multiple named sheets through VBA?
问题描述
我的工作簿包含大量数据,然后从连接中获取新数据,我必须从上个月获得的现有数据中清除它。我有下面的VB代码,但它只适用于单张纸。我该怎么做才能让它适用于多张床单。
我尝试过的事情:
Sub CleanTheTable()
Application.ScreenUpdating = False
Sheets(Active Roster)。选择
ActiveSheet。 ListObjects(ActiveRoster)。HeaderRowRange.Select
如果ActiveSheet.FilterMode那么
Selection.AutoFilter
结束如果
使用工作表(活动名册).ListObjects(ActiveRoster)
.Range.AutoFilter
On Error Resume Next
.DataBodyRange.Offset(1).Resize(。DataBodyRange.Rows.Count - 1,.DataBodyRange.Columns.Count).Rows.Delete
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
ActiveWindow.SmallScroll Down:= - 10000
End使用
Application.ScreenUpdating = True
End Sub
Sub CleanTheTable( sheetNo 作为 整数 )
Application.ScreenUpdating = False
表格( sheetNo )。选择
ActiveSheet.ListObjects( ActiveRoster)。HeaderRowRange。选择
如果 ActiveSheet.FilterMode 那么
Selection.AutoFilter
结束 如果
Dim tbl 作为 ListObject
对于 每个 tbl 在工作表(sheetNo).ListObjects
.Range.AutoFilter
On 错误 恢复 下一步
.DataBodyRange.Offset(< span class =code-digit> 1 )。调整大小(.DataBodyRange.Rows.Count - 1 ,。DataBodyRange.Columns.Count).Rows 。删除
.DataBodyRange.Rows( 1 )。SpecialCells(xlCellTypeConstants).ClearContents
ActiveWindow.SmallScroll Down:= - 10000
下一步
Application.ScreenUpdating = True
< span class =code-keyword>结束 Sub
然后你可以调用它所有表格类似于
公共 Sub CleanAll()
Dim i 作为 整数
对于 i = 1 表格。数
CleanTheTable i
下一步
结束 < span class =code-keyword> Sub
警告 - 我还没有对此进行全面测试,因此可能会出现一些轻微的输入错误
my workbook contains tons of data before get a fresh data from the connections i have to clear it from the existing data i got from the previous month. I have the VB code below but it will just work for a single sheet. what can i do to make it work for multiple sheets.
What I have tried:
Sub CleanTheTable()
Application.ScreenUpdating = False
Sheets("Active Roster").Select
ActiveSheet.ListObjects("ActiveRoster").HeaderRowRange.Select
If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If
With Worksheets("Active Roster").ListObjects("ActiveRoster")
.Range.AutoFilter
On Error Resume Next
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
ActiveWindow.SmallScroll Down:=-10000
End With
Application.ScreenUpdating = True
End Sub
Change your sub to be something like
Sub CleanTheTable(sheetNo As Integer) Application.ScreenUpdating = False Sheets(sheetNo).Select ActiveSheet.ListObjects("ActiveRoster").HeaderRowRange.Select If ActiveSheet.FilterMode Then Selection.AutoFilter End If Dim tbl As ListObject For Each tbl In Worksheets(sheetNo).ListObjects .Range.AutoFilter On Error Resume Next .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents ActiveWindow.SmallScroll Down:=-10000 Next Application.ScreenUpdating = True End Sub
Then you can call it for all sheets similar toPublic Sub CleanAll() Dim i As Integer For i = 1 To Sheets.Count CleanTheTable i Next End Sub
Caveat - I haven't fully tested this so there may be some minor typing errors
这篇关于如何通过VBA清除多个命名工作表中的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!