Rows with empty leave time should be automatically removed, but the code is not responding.
The contents of the sheet that I am trying to edit are as follows:
| company | name | birth | arrivetime | leavetime |
|---|---|---|---|---|
| AAA | John | 1998-09-18 | 9:23:53 | 17:23:43 |
| BBB | Michael | 1986-03-23 | 10:32:21 | |
| CCC | Kevin | 1978-02-12 | 18:23:51 |
Michael’s row must be deleted, but it doesn’t works at all
Dim ws As Worksheet
Dim LastRow As Integer
Dim i As Integer
Dim arriveTime As Variant
Dim leaveTime As Variant
Dim arrivehour As Variant
' Assign the currently active worksheet
Set ws = ThisWorkbook.ActiveSheet
' Find the last row
LastRow = Application.CountA(Range("c3", Range("c" & Rows.Count)))
' Loop backwards from the last row to row 3
For i = LastRow To 3 Step -1
arriveTime = ws.Cells(i, 4).value ' Arrival time (Column D)
leaveTime = ws.Cells(i, 5).value ' Leave time (Column E)
arrivehour = Hour(arriveTime)
' Check if the leave time is empty
If leaveTime = "" And arrivehour <= "17" Then ' If leave time is empty and arrival time is before 17:00
ws.Rows(i).Delete ' Delete the row
End If
Next i
' Release memory
Set ws = Nothing
MsgBox "Leave list has been created."
Initially, I thought the issue was with the variable types, but the same problem occurs even with different variable types.
1
First you should define your LastRow variable diferently like LastRow = ws.UsedRange.Rows.Count or LastRow = Application.CountA(Range("c", Range("c" & Rows.Count))) because as it is right now if you have 10 rows it will leave row 9 and 10 out of the loop.
Second, your condition arrivehour <= "17" should be arrivehour <= 17, I think that’s it.
3
I solved my problem with this code. Just re-coded
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Set the active worksheet
Set ws = ActiveSheet
' Find the last row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop from the last row to row 3 in reverse order
For i = lastRow To 3 Step -1
' Check if departure time is empty
If IsEmpty(ws.Cells(i, 5)) Then
' If check-in time is before 17:00, delete the row
If ws.Cells(i, 4).Value < TimeValue("17:00:00") Then
ws.Rows(i).Delete
End If
End If
Next i
MsgBox "Departure list has been created.", vbInformation
End Sub
1