Moving Rowes based on Date value with a macro

  • Thread starter Thread starter Max2073
  • Start date Start date
M

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.
 
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.
 
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
 
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.
 
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
 
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.
 
Back
Top