英文:
Is there a way to clear content in a dynamic range below a table?
问题
这里是您要翻译的内容:
"我在一个工作表上有3个表格,目前我已经使用VBA来调整表格1的大小时,自动调整表格2和3的大小。但当它们被调整大小时,是否可能清除表格2和3下方的所有内容,因为公式会留下来。
以下是调整大小的代码:
Dim Tbl_2 As ListObject
Dim Tbl_1 As ListObject
Dim Tbl_3 As ListObject
Set Tbl_1 = Sheet1.ListObjects("TableQuery")
Set Tbl_2 = Sheet1.ListObjects("Table2")
Set Tbl_3 = Sheet1.ListObjects("Table3")
If Tbl_3.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
Tbl_3.Resize Tbl_3.Range.Resize(Tbl_1.Range.Rows.Count)
End If
If Tbl_2.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
Tbl_2.Resize Tbl_2.Range.Resize(Tbl_1.Range.Rows.Count)
End If
以下是我理想情况下想要在表格调整大小后删除的图片内容:
英文:
I have 3 tables on one sheet and right now I have vba for resizing tables 2 and 3 whenevever table 1 is resized, but would it be possible to clear all the contents below tables 2 and 3 when they are resized, because the formulas are left over
Here's the resize code :
Dim Tbl_2 As ListObject
Dim Tbl_1 As ListObject
Dim Tbl_3 As ListObject
Set Tbl_1 = Sheet1.ListObjects("TableQuery")
Set Tbl_2 = Sheet1.ListObjects("Table2")
Set Tbl_3 = Sheet1.ListObjects("Table3")
If Tbl_3.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
Tbl_3.Resize Tbl_3.Range.Resize(Tbl_1.Range.Rows.Count)
End If
If Tbl_2.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
Tbl_2.Resize Tbl_2.Range.Resize(Tbl_1.Range.Rows.Count)
End If
And here's the picture of what I want to ideally remove once the table is resized :
答案1
得分: 1
我稍微调整了你的代码,因为我不确定是否允许同时更改两个表格,或者你可能会在这两个表格之间插入某些内容,所以我创建了一个额外的子程序以使代码更短。这将确保它只影响调整大小之前的单元格(因为你只调整行的大小,列保持不变)
Sub clearRowsAfterResizing()
Dim Tbl_2 As ListObject
Dim Tbl_1 As ListObject
Dim Tbl_3 As ListObject
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Test2")
Set Tbl_1 = ws.ListObjects("TableQuery")
Set Tbl_2 = ws.ListObjects("Table2")
Set Tbl_3 = ws.ListObjects("Table3")
changeSize Tbl_2, Tbl_1, ws
changeSize Tbl_3, Tbl_1, ws
End Sub
Sub changeSize(tblAdjust As ListObject, tblChanged As ListObject, ws As Worksheet)
Dim lRow As Long, dif As Long, sCol As Long, lCol As Long
lRow = tblAdjust.Range.Rows(tblAdjust.Range.Rows.Count).Row
dif = tblAdjust.Range.Rows.Count - tblChanged.Range.Rows.Count
If tblAdjust.Range.Rows.Count <> tblChanged.Range.Rows.Count Then
tblAdjust.Resize tblAdjust.Range.Resize(tblChanged.Range.Rows.Count)
If dif > 0 Then
sCol = tblAdjust.Range.Columns(1).Column
lCol = tblAdjust.Range.Columns(tblAdjust.Range.Columns.Count).Column
With ws
.Range(.Cells(lRow - dif + 1, sCol), .Cells(lRow, lCol)).Clear
End With
End If
End If
End Sub
希望这对你有所帮助,如果有任何问题,请随时提问
英文:
I have adjusted your code a bit as I wasn't sure if I was allowed to change both tables at the same time or if you might place certain things in between those tables so I made an extra sub to make it shorter(ish). This will make sure it only affects the cells it had prior to the resize (since you're only resizing the rows, the columns remain the same)
Sub clearRowsAfterResizing()
Dim Tbl_2 As ListObject
Dim Tbl_1 As ListObject
Dim Tbl_3 As ListObject
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Test2")
Set Tbl_1 = ws.ListObjects("TableQuery")
Set Tbl_2 = ws.ListObjects("Table2")
Set Tbl_3 = ws.ListObjects("Table3")
changeSize Tbl_2, Tbl_1, ws
changeSize Tbl_3, Tbl_1, ws
End Sub
Sub changeSize(tblAdjust As ListObject, tblChanged As ListObject, ws As Worksheet)
Dim lRow As Long, dif As Long, sCol As Long, lCol As Long
lRow = tblAdjust.Range.Rows(tblAdjust.Range.Rows.Count).Row
dif = tblAdjust.Range.Rows.Count - tblChanged.Range.Rows.Count
If tblAdjust.Range.Rows.Count <> tblChanged.Range.Rows.Count Then
tblAdjust.Resize tblAdjust.Range.Resize(tblChanged.Range.Rows.Count)
If dif > 0 Then
sCol = tblAdjust.Range.Columns(1).Column
lCol = tblAdjust.Range.Columns(tblAdjust.Range.Columns.Count).Column
With ws
.Range(.Cells(lRow - dif + 1, sCol), .Cells(lRow, lCol)).Clear
End With
End If
End If
End Sub
Hope this helps, if you have any questions feel free to ask
答案2
得分: 0
重设 'Slave' 表格大小
Option Explicit
Sub ResizeSlaveTables()
' 定义常量。
Const MASTER_NAME As String = "TableQuery"
Const SLAVE_NAMES As String = "Table2,Table3"
' 引用工作表(通过代码名称)。很好!
Dim ws As Worksheet: Set ws = Sheet1
' 将从属表格名称拆分为数组。
Dim Slaves() As String: Slaves = Split(SLAVE_NAMES, ",")
' 引用主表格。
Dim mlo As ListObject: Set mlo = ws.ListObjects(MASTER_NAME)
' 计算其行数。
Dim mrCount As Long: mrCount = mlo.Range.Rows.Count
Dim Slave, srCount As Long, WasResized As Boolean
' 循环遍历从属表格名称。
For Each Slave In Slaves
' 引用从属表格的范围。
With ws.ListObjects(Slave).Range
' 计算其行数。
srCount = .Rows.Count
' 根据要求执行操作。
If mrCount <> srCount Then ' 大小不同
.ListObject.Resize .Resize(mrCount) ' 调整从属表格大小
If mrCount < srCount Then ' 从属表格大小更大
' 清除从属表格新大小下方的 '遗留' 部分。
.Resize(srCount - mrCount).Offset(mrCount).Clear
'Else ' 主表格大小更大;不执行任何操作
End If
WasResized = True ' 用于消息框
'Else ' 大小相同;不执行任何操作
End If
End With
Next Slave
If WasResized Then ' 至少有一个已调整大小
MsgBox "从属表格已调整大小。", vbInformation
Else ' 没有调整大小的表格
MsgBox "从属表格大小正确。", vbExclamation
End If
End Sub
英文:
Resize 'Slave' Tables
<!-- language: lang-vb -->
Option Explicit
Sub ResizeSlaveTables()
' Define constants.
Const MASTER_NAME As String = "TableQuery"
Const SLAVE_NAMES As String = "Table2,Table3"
' Reference the worksheet (by code name). Great!
Dim ws As Worksheet: Set ws = Sheet1
' Split the slave table names into an array.
Dim Slaves() As String: Slaves = Split(SLAVE_NAMES, ",")
' Reference the master table.
Dim mlo As ListObject: Set mlo = ws.ListObjects(MASTER_NAME)
' Calculate its number of rows.
Dim mrCount As Long: mrCount = mlo.Range.Rows.Count
Dim Slave, srCount As Long, WasResized As Boolean
' Loop through the slave table names.
For Each Slave In Slaves
' Reference the range of the slave table.
With ws.ListObjects(Slave).Range
' Calculate its number of rows.
srCount = .Rows.Count
' Act per requirement.
If mrCount <> srCount Then ' the sizes are different
.ListObject.Resize .Resize(mrCount) ' resize the slave
If mrCount < srCount Then ' slave size was greater
' Clear 'remains' below the new size of the slave.
.Resize(srCount - mrCount).Offset(mrCount).Clear
'Else ' master size was greater; do nothing
End If
WasResized = True ' for the message box
'Else ' the sizes are the same; do nothing
End If
End With
Next Slave
If WasResized Then ' at least one was resized
MsgBox "Slave tables resized.", vbInformation
Else ' none were resized
MsgBox "The slave tables were of the correct size.", vbExclamation
End If
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论