vba - 需要根据Excel VBA中当前单元格中的单元格值删除Excel工作表中的上一行

Question

我正在为该项目编写第一个宏，该项目具有从运动传感器导出的以下数据。在每个“检测到运动”行之后都有一个“无运动”。这个“无 Action ”是该传感器固件中的一个故障，我无法编辑。所以它看起来像这样:

DataMessageGUID SensorID    Sensor Name Date    Value   Formatted Value Battery
5d6f57c1-7449-4da3-a9fa-9709ae4b0671    301232  Bedroom Left    8/4/2017 8:57   0   No Motion   100
adaa026e-8847-4eab-ac48-6ac93fc93f16    301232  Bedroom Left    8/4/2017 8:56   1   Motion Detected 100
0b4287f2-258e-48d5-97e6-e00ed3c68a6a    301232  Bedroom Left    8/4/2017 8:56   0   No Motion   100
0a3ba320-f8e5-4ae9-97ac-26c5269e811e    301232  Bedroom Left    8/4/2017 8:55   1   Motion Detected 100
1946aa41-fbaf-4a0d-8e5e-c846ef0cdb24    301232  Bedroom Left    8/4/2017 8:55   0   No Motion   100
ae214e59-c93f-4832-bfd9-5c011a60435a    301232  Bedroom Left    8/4/2017 8:54   1   Motion Detected 100
c2518ff0-053c-49fd-99e6-e6ed20798279    301232  Bedroom Left    8/4/2017 8:22   0   No Motion   100
e1678d9e-c919-4378-8021-cc669b45918a    301232  Bedroom Left    8/4/2017 8:21   1   Motion Detected 100
ed8c20ee-3630-4b68-8063-788b25f7c433    301232  Bedroom Left    8/4/2017 8:19   0   No Motion   100
7d537313-9133-4525-9ef8-fcb1fbce81ad    301232  Bedroom Left    8/4/2017 8:19   1   Motion Detected 100
dcaa463c-0ff0-4a17-9881-f67641fae014    301232  Bedroom Left    8/4/2017 8:16   0   No Motion   100
c66d16d2-063b-4aca-bd81-3abdaaa3063f    301232  Bedroom Left    8/4/2017 8:15   1   Motion Detected 100
1fbf6e16-c8e8-4a18-ae71-42195fcf6e48    301232  Bedroom Left    8/4/2017 8:14   0   No Motion   100
7f5ed16c-ab3d-411e-a2c7-851eeb5641d4    301232  Bedroom Left    8/4/2017 8:13   1   Motion Detected 100
63f79047-143b-4b17-a359-6d6eac1d3a43    301232  Bedroom Left    8/4/2017 8:13   0   No Motion   100
52f857f2-5b5b-4fec-b7da-67390236f473    301232  Bedroom Left    8/4/2017 8:12   1   Motion Detected 100
19bce92e-0068-4f46-ac1d-a79877c88a0c    301232  Bedroom Left    8/4/2017 8:12   0   No Motion   100

我需要删除“检测到运动”行后立即显示的“无运动”。

在我编辑的在线代码的帮助下，我得到了这个:

Sub Macro_Delete_rows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow    As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

With ActiveSheet
    .Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    .DisplayPageBreaks = False

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For Lrow = Lastrow To Firstrow Step -1
        With .Cells(Lrow, "F")
            If Not IsError(.Value) Then
                If .Value = "Motion Detected" Then .Rows(Lrow - 1).EntireRow.Delete
                'Here I try to delete previous row to this row
            End If
        End With
    Next Lrow
End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With

End Sub

这并没有真正起作用，而且大多数情况下我最终删除了错误的行。我哪里出错了？

感谢您的帮助!

Answer 1

尝试将此作为您的 For 循环:

  For Lrow = Lastrow To Firstrow Step -1
        With .Cells(Lrow, "F")
            If Not IsError(.Value) Then
                If .Value = "No Motion" And .Offset(-1, 0) = "Motion Detected" Then
                    .EntireRow.Select
                    .EntireRow.Delete
                End If
            End If
        End With
    Next Lrow

其想法是查看当前行是否为“无运动”并且前一行是否为“检测到运动”。如果是这样，请删除“无 Action ”行。

您的原始代码处于正确的轨道上，但请注意，它不会删除最后一个“无运动”行，因为它后面没有“检测到运动”行。