有没有一种方法可以清除表格下面的动态范围中的内容?

huangapple go评论59阅读模式
英文:

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(&quot;TableQuery&quot;)
Set Tbl_2 = Sheet1.ListObjects(&quot;Table2&quot;)
Set Tbl_3 = Sheet1.ListObjects(&quot;Table3&quot;)

If Tbl_3.Range.Rows.Count &lt;&gt; 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 &lt;&gt; 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(&quot;Test2&quot;)
    Set Tbl_1 = ws.ListObjects(&quot;TableQuery&quot;)
    Set Tbl_2 = ws.ListObjects(&quot;Table2&quot;)
    Set Tbl_3 = ws.ListObjects(&quot;Table3&quot;)
    
    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 &lt;&gt; tblChanged.Range.Rows.Count Then
        tblAdjust.Resize tblAdjust.Range.Resize(tblChanged.Range.Rows.Count)
        If dif &gt; 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()

    &#39; Define constants.
    Const MASTER_NAME As String = &quot;TableQuery&quot;
    Const SLAVE_NAMES As String = &quot;Table2,Table3&quot;
    
    &#39; Reference the worksheet (by code name). Great!
    Dim ws As Worksheet: Set ws = Sheet1
    
    &#39; Split the slave table names into an array.
    Dim Slaves() As String: Slaves = Split(SLAVE_NAMES, &quot;,&quot;)

    &#39; Reference the master table.
    Dim mlo As ListObject: Set mlo = ws.ListObjects(MASTER_NAME)
    &#39; Calculate its number of rows.
    Dim mrCount As Long: mrCount = mlo.Range.Rows.Count
    
    Dim Slave, srCount As Long, WasResized As Boolean
    
    &#39; Loop through the slave table names.
    For Each Slave In Slaves
        &#39; Reference the range of the slave table.
        With ws.ListObjects(Slave).Range
            &#39; Calculate its number of rows.
            srCount = .Rows.Count
            &#39; Act per requirement.
            If mrCount &lt;&gt; srCount Then &#39; the sizes are different
                .ListObject.Resize .Resize(mrCount) &#39; resize the slave
                If mrCount &lt; srCount Then &#39; slave size was greater
                    &#39; Clear &#39;remains&#39; below the new size of the slave.
                    .Resize(srCount - mrCount).Offset(mrCount).Clear
                &#39;Else &#39; master size was greater; do nothing
                End If
                WasResized = True &#39; for the message box
            &#39;Else &#39; the sizes are the same; do nothing
            End If
        End With
    Next Slave
    
    If WasResized Then &#39; at least one was resized
        MsgBox &quot;Slave tables resized.&quot;, vbInformation
    Else &#39; none were resized
        MsgBox &quot;The slave tables were of the correct size.&quot;, vbExclamation
    End If
    
End Sub

huangapple
  • 本文由 发表于 2023年3月4日 00:09:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/75629413.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定