VBA為什么DeleteShiftUp會洗掉它下面的所有內容？

因此，每當在作業表上添加名稱時，我的代碼都會將該名稱添加到所有其他作業表中，并且每當從同一個作業表中洗掉一個名稱時，它應該從所有其他作業表中洗掉（這些是下面選定的作業表）。但是由于某種原因，運行也會ActiveCell.EntireRow.Delete Shift:=xlUp洗掉下面的所有內容ActiveCell？這是我目前的全部代碼。

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cCol As String = "A"
    Const fRow As Long = 2
    
    Dim crg As Range
    Dim ddFound As Range
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim outpt As String
    Dim i As Integer

    Set crg = Worksheets("Statistics").Columns(cCol).Resize(Rows.Count - fRow   1).Offset(fRow - 1)
    Dim irg As Range: Set irg = Intersect(crg, Target)
    Dim sraddress As String
    Dim statdel As Range
    
    Dim dws As Worksheet
    Dim ddcrg As Range
    Dim statrange As Range
    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        sraddress = CStr(irg.Value)
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each ws In ActiveWorkbook.Worksheets
            Set ddcrg = ws.Columns(cCol)
            Set ddFound = ddcrg.Find(sraddress, , xlValues, xlWhole)
            
            If sraddress <> "" Then
                irg.Select:   ActiveCell = irg.Value2
                irg.Copy
                ws.Range(irg.Address) = irg.Value2
                Application.CutCopyMode = False
            ElseIf sraddress = "" Then
                Dim Deladdrs As String
                irg.EntireRow.Select
            
                On Error Resume Next

                Sheets(Array("Statistics", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")).Select
            
                Deladdrs = ActiveCell.Address(0, 0)
                
                ActiveCell.EntireRow.Delete Shift:=xlUp
        
                Application.CutCopyMode = False
            Else
            End If
        Next ws
    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    Else
    End If
End Sub

uj5u.com熱心網友回復：

請測驗下一個代碼。它假定只有陣列中的作業表（來自您的代碼）應該被更新。而且，在洗掉名稱的情況下，包含它的行也應該被洗掉。該代碼還涵蓋了行洗掉的情況，否則應將值放在Target要更新的作業表的所有行的新的 A:A 中：

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cCol As String = "A", fRow As Long = 2
    
    Dim crg As Range, ws As Worksheet
    Dim irg As Range, irgVal As String

    If Target.Rows.Count > 1 then exit sub. Not allowed more rows to be deleted, changed.
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Set crg = Me.Columns(cCol).Resize(rows.Count - fRow   1).Offset(fRow - 1)
        Set irg = Intersect(crg, Target)
        irgVal = irg.value
        If Target.Columns.Count = 16384 Then irgVal = "" 'for the case of deleting the whole row!
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each ws In Worksheets(Array("January", _
                               "February", "March", "April", "M)
            
            If irgVal <> "" Then
                ws.Range(Target.Address).value = irg.value
            Else
                ws.rows(irg.row).EntireRow.Delete
            End If
        Next ws
        If CStr(irg.value) = "" Then irg.EntireRow.Delete 'delete also the Target row...

        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

上面的代碼不是為多行洗掉設計的，改

可以設計觸發行洗掉（在功能區級別）的事件，但它不會使問題的物件......

標籤：

上一篇：生成一個隨機逗號分隔的產品ID串列，然后將它們的值相加？

下一篇：匹配、復制、粘貼和清除需要很長時間。如何加快速度？