Excel - Moving Rowes based on Date value with a macro

Asked By Max2073 on 07-Sep-09 05:26 PM
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 replied on 07-Sep-09 05:52 PM
Cant you sort or filter and then cut>paste>delete blank rows.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Max2073 replied on 07-Sep-09 07:03 PM
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 replied on 08-Sep-09 01:04 AM
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
---------------
Jacob Skaria
Max2073 replied on 08-Sep-09 01:41 AM
Thank you very much.  Is just what I needed.  Once again thank you.
Max2073 replied on 08-Sep-09 01:48 AM
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 replied on 08-Sep-09 03:29 AM
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
---------------
Jacob Skaria
Don Guillett replied on 08-Sep-09 08:54 AM
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com