vba - 自动筛选删除仅在从特定工作表运行宏时有效

问题描述

我有这个宏，它基本上使用两个工作表 - sheet2 更新 sheet1 然后杀死第二个工作表。

我注意到，当涉及到宏的一部分时（删除在工作表 1 的 A 列中具有“删除”的行），如果我从工作表 2 运行宏，它似乎不起作用。如果我从工作表 1 运行它是没有问题的。

这是完整的代码，以防万一您需要查看它 - 我将突出显示我接下来遇到问题的部分。：

 Public Sub Cable_Load_full()
'~~> Copy New Accounts from worksheet2
Dim ws1 As Worksheet, ws2 As Worksheet
Dim bottomL As Integer
Dim x As Integer
Dim c As Range
Dim i As Long, J As Long, LastCol As Long
Dim ws1LR As Long, ws2LR As Long
Dim ws1Rng As Range, aCell As Range
Dim SearchString

Set ws1 = Sheets("CableSocials")
Set ws2 = Sheets("CableRevised")
bottomL = ws2.Range("A" & Rows.Count).End(xlUp).Row: x = 1
x = ws1.Range("A" & Rows.Count).End(xlUp).Row
x = x + 1

For Each c In ws2.Range("A1:A" & bottomL)
 If c.Value = "New" Then
    c.EntireRow.Copy ws1.Range("A" & x)
    x = x + 1
End If
Next c

'~~> Assuming that ID is in Col B
'~~> Get last row in Col B in Sheet1
ws1LR = ws1.Range("B" & Rows.Count).End(xlUp).Row
'~~> Set the Search Range
Set ws1Rng = ws1.Range("B1:B" & ws1LR)
'~~> Adding Revise Column to worksheet 1
ws1.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Value = "Revise"

Set ws2 = Sheets("CableRevised")
'~~> Turn off Filter
ws2.AutoFilterMode = False

'~~> Get last row in Col A in Sheet2
ws2LR = ws2.Range("B" & Rows.Count).End(xlUp).Row

'~~> Loop through the range in Sheet 2 to match it with the range in Sheet1
For i = 1 To ws2LR
    SearchString = ws2.Range("B" & i).Value

    '~~> Search for the ID
    Set aCell = ws1Rng.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)


    '~~> If found
    If Not aCell Is Nothing Then
        '~~> Append values
            ws1.Cells(aCell.Row, 1).Value = ws2.Cells(i, 1).Value
            ws1.Cells(aCell.Row, 3).Value = ws2.Cells(i, 2).Value
            ws1.Cells(aCell.Row, 19).Value = ws2.Cells(i, 18).Value
            ws1.Cells(aCell.Row, 20).Value = ws2.Cells(i, 19).Value
End If
Next i
    '~~> Delete the accounts that need to be deleted
     ws1.AutoFilterMode = False
        With Range("A1", Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "Delete"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
 End With
     ws1.AutoFilterMode = False

     '~~> Removing New from Column B

    ws1.Columns("B").Replace What:="New", _
                         Replacement:="", _
                         LookAt:=xlPart, _
                         SearchOrder:=xlByRows, _
                         MatchCase:=False, _
                         SearchFormat:=False, _
                         ReplaceFormat:=False

    ws1.Columns("A").EntireColumn.Delete

    Call SheetKiller

End Sub

Sub SheetKiller()
  Dim s As Worksheet, t As String
Dim i As Long, K As Long
K = Sheets.Count

For i = K To 1 Step -1
    t = Sheets(i).Name
    If t = "CableRevised" Then
        Application.DisplayAlerts = False
            Sheets(i).Delete
        Application.DisplayAlerts = True
    End If
Next i
End Sub

因此，仅当我从 Sheet1 运行宏时才起作用的部分是：

  '~~> Delete the accounts that need to be deleted
     ws1.AutoFilterMode = False
        With Range("A1", Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "Delete"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
    ws1.AutoFilterMode = False

我不知道为什么 - 它是否表现得好像它只会从 ActiveSheet 中删除行（我猜这将是我运行宏的工作表？）？即使我从 Sheet2 运行宏，是否可以使其工作？

感谢您提供的任何帮助！

标签： vbaexcel

'~~> Delete the accounts that need to be deleted ws1.AutoFilterMode = False With ws1.Range("A1", ws1.Range("A" & ws1.Rows.Count).End(xlUp)) .AutoFilter 1, "Delete" On Error Resume Next .Offset(1).SpecialCells(12).EntireRow.Delete End With ws1.AutoFilterMode = False

vba - 自动筛选删除仅在从特定工作表运行宏时有效

问题描述

解决方案

推荐阅读