Using Now() Command in VBA

Satch7

New Member
Joined
Jul 30, 2007
Messages
9
I am trying to get the macro to run the following:

Select all data in one sheet (All) and pass into another (Due) then I am asking for it to look at the date in cell F2 and delete the entire row should the date be greater than today date (now()). This should show all the risks and issues that have a raised on a date of less than today's date.

Attached is a copy of the code with the offending piece of code in red Sub due()
Sheets("All").Select
Range("A1:m50").Select
Selection.Copy
Sheets("Due").Select
Range("A1").Select
ActiveSheet.Paste
Range("f2").Select
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To 50
If ActiveCell.Value > Now Then
Selection.EntireRow.Delete



Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = False
End Sub



The macro runs fine and I can get it to delete all the data row by row however it continues to delete the rows where the date is after today's date, which is shouldn’t do.


Can anyone help?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

This line:

If ActiveCell.Value > Now Then

is telling the macro to delete the cell if its value exceeds today's date - is this what you want?
 

Satch7

New Member
Joined
Jul 30, 2007
Messages
9
Yes that right.

It should be looking at the date and then after the macro has run then the only risks that show are the one with a date less than todays date.

Any risk with a raised on date which is post today's date then the whole line should be deleted.

Obviously I need to change the name of the "Raised Date" to completed date :biggrin:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
So the problem is it is also additionally deletes any rows where the date is before today's date? Do you actually have numerical Excel dates in these cells, or are they text?
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Code:
Sub test()
    Dim i As Long
    Application.ScreenUpdating = False

    Sheets("All").Range("A1:M50").Copy
    With Sheets("Due")
        .Range("A1").Paste

        For i = 1 To 50 Step -1

            If .Cells(i, 6).Value> Now Then Rows("i:i").EntireRow.Delete

        Next i
    End With
    Application.ScreenUpdating = False
End Sub

If the above causes the same problems that you're currently having, then your 'dates' in column F may actually be text.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Satch7, welcome to the board.
Here's an example of your (amended) code that will work:
Code:
Sub due()
Sheets("All").Range("A1:m50").Copy Sheets("Due").Range("A1")
Application.ScreenUpdating = False
With Sheets("Due")
  For i = 50 To 1 Step -1
    If .Cells(i, "F") > Now Then .Rows(i).EntireRow.Delete
  Next i
End With
Application.ScreenUpdating = False
End Sub

But, (if you're open to another suggestion) you can do this using the AutoFilter instead of a loop.
(Assumes you have a headers in row 1 of the "All" (and consequently) the "Due" sheet)
Code:
Sub due()
Sheets("All").Range("A1:m50").Copy Sheets("Due").Range("A1")
With Sheets("Due")
  .Columns("F:F").AutoFilter Field:=1, Criteria1:=">" & Date, Operator:=xlAnd
  .Range("F2:F50").SpecialCells(xlCellTypeVisible).EntireRow.Delete
  .AutoFilterMode = False
End With
End Sub

Hope it helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,833
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top