I’m trying to move a row based on date to another sheet.
The code works and the row moves correctly, but only when all the fields are filled in with a date.
- I also want to move the row when one or more fields are empty, so it only checks the columns that have a date filled in between the range F-H.
- Is tere a way to add all the columns that need to be checked in one line of code? Instead of hardcoding every column?
Code below.
Thanks in advance.
Sub MoveBasedOnValue5()
lastrowcurrent = Sheets("Verlopen Keuring").Range("A" & Rows.Count).End(xlUp).Row
lastrowpost = Sheets("Afgehandeld").Range("A" & Rows.Count).End(xlUp).Row
For x = lastrowcurrent To 2 Step -1
If Sheets("Verlopen keuring").Range("F" & x) >= Date And Sheets("Verlopen keuring").Range("G" & x) >= Date And Sheets("Verlopen keuring").Range("H" & x) >= Date Then
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Cut Sheets("Afgehandeld").Range("A" & lastrowpost + 1)
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Delete
lastrowpost = lastrowpost + 1
End If
Next
End Sub
You can use the EVALUATE for worksheet functions something like this:
If Evaluate("=if(or('Verlopen keuring'!F" & x & ":H" & x & ">=Now()),true,false)") Then
You should use OR IsEmpty():
Sub MoveBasedOnValue5()
lastrowcurrent = Sheets("Verlopen Keuring").Range("A" & Rows.Count).End(xlUp).row
lastrowpost = Sheets("Afgehandeld").Range("A" & Rows.Count).End(xlUp).row
For x = lastrowcurrent To 2 Step -1
If (Sheets("Verlopen keuring").Range("F" & x) >= Date Or IsEmpty(Sheets("Verlopen keuring").Range("F" & x)) _
And (Sheets("Verlopen keuring").Range("G" & x) >= Date Or IsEmpty(Sheets("Verlopen keuring").Range("G" & x))) _
And (Sheets("Verlopen keuring").Range("H" & x) >= Date Or IsEmpty(Sheets("Verlopen keuring").Range("H" & x)))) Then
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Cut Sheets("Afgehandeld").Range("A" & lastrowpost + 1)
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Delete
lastrowpost = lastrowpost + 1
End If
Next
End Sub
Just make sure to add proper logic if all three cells F,G and H are empty.
This will be the same functionality:
Sub MoveBasedOnValue6()
lastrowcurrent = Sheets("Verlopen Keuring").Range("A" & Rows.Count).End(xlUp).row
lastrowpost = Sheets("Afgehandeld").Range("A" & Rows.Count).End(xlUp).row
For x = lastrowcurrent To 2 Step -1
If Not ((Sheets("Verlopen keuring").Range("F" & x) < Date) Or (Sheets("Verlopen keuring").Range("G" & x) < Date) Or (Sheets("Verlopen keuring").Range("H" & x) < Date)) Then
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Cut Sheets("Afgehandeld").Range("A" & lastrowpost + 1)
Sheets("Verlopen keuring").Range("A" & x).EntireRow.Delete
lastrowpost = lastrowpost + 1
End If
Next
End Sub
New contributor
Vlado Bošnjaković is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Option Explicit
Sub MoveBasedOnValue5()
Dim rngTo As Range, c As Range
Dim lastrowcurrent As Long, n As Long, r As Long
With Sheets("Afgehandeld")
Set rngTo = .Range("A" & .Rows.Count).End(xlUp)
End With
With Sheets("Verlopen keuring")
lastrowcurrent = .Range("A" & .Rows.Count).End(xlUp).Row
For r = lastrowcurrent To 2 Step -1
For Each c In .Range("F:H").Columns
' add more columns like this
'For Each c In .Range("F:H,J:L,O:O").Columns
If c.Rows(r) >= Date Then
Set rngTo = rngTo.Offset(1)
.Rows(r).Copy rngTo
.Rows(r).Delete
n = n + 1
Exit For
End If
Next
Next
End With
MsgBox n & " rows moved"
End Sub