Comparing Dates with VBA

[Excel Ninja In Training]

I am trying to compare the date in an active cell to a reference date in cell U1, using:

If "U1" > (ActiveCell) Then

I've got a loop that is evaluating this in turn for each cell in the B column. Sadly, Excel is always interpreting the above statement to be true, even when U1< the active cell (i.e. the active cell is more recent than the reference date).

Here's the full code:

Sub test4()
Sheets("Consolidate").Select
Range("B2").Select
Do
If "U1" > (ActiveCell) Then
ActiveCell.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
​

When I run this code, Excel considers each row and then deletes it, regardless of whether the U1 is greater than or less than (i.e. newer than or older than) the active cell.

As a reference, to make sure that I'm doing things correctly, I entered the formula "=U$1>B2" into cell C2 and filled down the whole C column. This correctly gave me "true" when the corresponding B cell was older than the reference date and "false" when it was newer, so the code above is my effort to duplicate that formula in VBA code.

Can anyone tell me what I'm doing wrong?

 

[njimack]

Sub test4()
Dim Last_Row as Long
Dim i as Long
With Sheets("Consolidate")

Last_Row = Range("B" & Rows.Count).End(xlUp).Row

For i = Last_row to 2 Step -1

If Cells(i,2)< Range("U1") Then Cells(i,2).EntireRow.Delete

Next i

End With
End Sub

 

[Excel Ninja In Training]

Thanks for the reply, njimack. I've tried running your code, and it didn't actually have any apparent effect - it didn't delete any rows at all, even the ones that I was hoping that it would delete.

However, in the interim, I kept Googling and working on my own code, and I've got it working the way that I wanted it to. Here's the final version that I came up with:

Sub test4()
Sheets("Consolidate").Select
Range("B2").Select
Do
If ActiveSheet.Range("S1") > ActiveCell Then
ActiveCell.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

As you can see, I'm desperately avoiding using variables - not that I have any objection in principle, but just because I'm new to VBA and not comfortable with them yet. So what I've written may not be elegant, but at least it does what I was looking for.

 

[njimack]

The main thing is your code is working, but it's not very efficient. As a general rule, you don't need to select cells to perform actions on them.

I've corrected my code, which should now work - I'd forgotton to qualify the ranges.

Sub test4()

Application.ScreenUpdating = False

Dim Last_Row as Long
Dim i as Long
With Sheets("Consolidate")

Last_Row = .Range("B" & Rows.Count).End(xlUp).Row

For i = Last_row to 2 Step -1

If .Cells(i,2)< .Range("U1") Then .Cells(i,2).EntireRow.Delete

Next i

End With

Application.ScreenUpdating = True

End Sub

 

[Excel Ninja In Training]

Thank you. Your code worked beautifully. I'll be studying it to learn from it.

 

[njimack]

Glad it worked, thanks for the feedback.