Need to delete previous row in Excel sheet based on cell value in current cell in Excel VBA
I am writing my first macro for this project which has the following export data from a motion sensor. After each "Motion Detected" it has "No Motion". This "No Movement" is a bug in the sensor firmware that I cannot edit. So it looks like this:
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
I need to remove the "No Motion" that appears right after the "Motion Detected" lines.
With the help of the code I edited online, I got this:
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
It doesn't work and basically I am deleting the wrong lines. Where am I going wrong?
Thanks for the help!
source to share
Try it like a loop 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
The idea is that it looks to see if the current line shows No Motion and the previous line shows Motion Detected. If so, remove the No Movement line.
Your original code was on the right track, but note that it will not remove the last No Motion line as there is no Motion Detected line after it.
source to share
This will solve your problem.
Sub Macro_Delete_rows()
Dim TotalRows As Long
TotalRows = Application.WorksheetFunction.CountA(Sheets("yourSheetName").Range("A:A"))
For i = 2 To TotalRows
'You can delete the data by 0 before No Moton Cell
'if G colomn has No Motion
Range("G" & i).Select
If ActiveCell.Value = "No Motion" Then
ActiveCell.EntireRow.Delete
i = 1 - 1
End If
' 'If F column has 0
' Range("F" & i).Select
' If ActiveCell.Value = 0 Then
' ActiveCell.EntireRow.Delete
' i = 1 - 1
' End If
Next i
End Sub
source to share