excel 运行时错误“13”：从合并单元格中删除数据

tyu7yeag 于 2023-08-08 发布在其他

关注(0)|答案(1)|浏览(97)

我没有VBA的经验，一直试图通过谷歌我的方式通过一些功能，我想。当前的目标是根据另一个单元格的输入隐藏一行。这部分工作，但如果数据被删除，我得到的错误。代码在下面。

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("U8:Y8"), Range(Target.Address)) Is Nothing Then

        Select Case Target.Value
            Case Is = "Owner": Rows("11:11").EntireRow.Hidden = True
            Case Is = "Renter":  Rows("11:11").EntireRow.Hidden = False
            Case Is = "":  Rows("11:11").EntireRow.Hidden = False
        End Select
                                              
End If

If Not Application.Intersect(Range("U8:Y8"), Range(Target.Address)) Is Nothing Then

        Select Case Target.Value
            Case Is = "Renter": Rows("18:18").EntireRow.Hidden = True
            Case Is = "Owner":  Rows("18:18").EntireRow.Hidden = False      
            Case Is = "":  Rows("18:18").EntireRow.Hidden = False
        End Select
End If

If Not Application.Intersect(Range("G22:K22"), Range(Target.Address)) Is Nothing Then

        Select Case Target.Value
            Case Is = "Mail": Rows("23:23").EntireRow.Hidden = True
            Case Is = "E-Billing":  Rows("23:23").EntireRow.Hidden = False        
            Case Is = "":  Rows("23:23").EntireRow.Hidden = False        
        End Select
                
End If

End Sub

字符串
最初的范围只是“U8”和“G22”，我想如果我包括每个合并区域的全部范围，它会工作，但没有。

excel

来源：https://stackoverflow.com/questions/76756968/run-time-error-13-deleting-data-from-a-merged-cell

1条答案

按热度按时间

ctehm74n1#

当 * 输入 * 一个值到一个合并的单元格中时，它会触发带有单个单元格Target（即Change）的Change事件。Target.Cells.CountLarge = 1），但是当从合并单元格中 * 删除 * 一个值时，它会触发多单元格Target（在本例中为Target.Cells.CountLarge = 5），因此您需要在事件处理代码中说明这一点。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v, c As Range
    
    Debug.Print Target.Address, Target.Cells.CountLarge, TypeName(Target.Value)
    
    'Handle the multi-cell Target when a value is deleted from a merged cell
    If Target.Cells(1).MergeArea.Address = Target.Address Then 'is Target a merged cell?
        Set c = Target.Cells(1) 'get the first cell in the mergearea
    Else
        Set c = Target 'regular case...
    End If
    If c.Cells.CountLarge > 1 Then Exit Sub
    
    Select Case c.Address(False, False)
        Case "U8"
            Select Case c.Value
                Case "Owner"
                    Me.Rows(11).Hidden = True
                    Me.Rows(18).Hidden = False
                Case "Renter"
                    Me.Rows(11).Hidden = False
                    Me.Rows(18).Hidden = True
                Case ""
                    Me.Rows(11).Hidden = False
                    Me.Rows(18).Hidden = False
            End Select
        Case "G22"
            Select Case c.Value
                Case "Mail": Me.Rows(23).Hidden = True
                Case "E-Billing", "": Me.Rows(23).Hidden = False
            End Select
    End Select

End Sub

字符串

赞(0）回复(0）举报 2023-08-08

我来回答

excel 运行时错误“13”：从合并单元格中删除数据

1条答案

相关问题

热门标签

最新问答