Moving Rowes based on Date value with a macro

[Max2073]

Column D in my spreadsheet contains either a Date value or the word "NULL".
I would like to move all rows with a date value in Column D to Sheet 2 by
macro. The original row should be removed from Sheet1.

 

[Don Guillett]

Cant you sort or filter and then cut>paste>delete blank rows.

 

[Max2073]

The reason that I wanted it as a macro was so that I can incorporate it into
to another marco that deletes rows meeting a set criteria, the marco that I
am currently working on also moves rows that meet a set criteria (employee
name). However, I can not work out how to move rows based on the date.

 

[Jacob Skaria]

Hi Max

Try the below macro

Sub MyMacro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Sheet2")
lngNRow = ws2.Cells(Rows.Count, "D").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("D" & lngRow)) Then
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Max2073]

Thank you very much. Is just what I needed. Once again thank you.

 

[Max2073]

Sorry, to bug you.

I just ran the macro and have noticed that the macro appears to copy the
rowes, however when I have looked into Sheet2 there is only one row there.
The delete function works fine.

 

[Jacob Skaria]

Max; I have missed to increment the row number...Try the below

Sub MyMacro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Sheet2")
lngNRow = ws2.Cells(Rows.Count, "D").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("D" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Don Guillett]

You should have posted all of your macro.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.