Today's Date
 

I need to convert the date to MM/DD/YYYY format from MM/DD/YY and then delete
all rows in the current worksheet that don't have today's date in column C.
The number of rows is variable each day. This is the code I was using,
however it does not work now and I'm not sure why, any help you can provide
is appreciated.

Sub OnlyTodaysDate()

' Change date to read format mm/dd/yyyy

Dim RowNum As Integer
Dim NextValue As String
Dim NextDate As Date

For RowNum = 1 To Range("C1").CurrentRegion.Rows.Count
NextValue = Range("C" & RowNum).Value
NextDate = DateSerial(2000 + Mid(NextValue, 1, 1), Mid(NextValue, 2,
2), Mid(NextValue, 4, 2))
Range("C" & RowNum).NumberFormat = "mm/dd/yyyy"
Range("C" & RowNum).Value = NextDate
Next RowNum


'Deletes rows where the value in column C is not today's date

Dim RowNdx4 As Long
Dim LastRow4 As Long

LastRow4 = Cells(Rows.Count, "C").End(xlUp).Row

For RowNdx4 = LastRow4 To 1 Step -1

If Cells(RowNdx4, "C").Value = FormatDateTime(Now, vbShortDate) =
False Then
Rows(RowNdx4).Delete
End If

Next RowNdx4

End Sub

Today's Date
 

Am I missing something? If I want to test if a cell's value matches today's
date why do I need to reformat the cell? The sub below worked for me. The
date in C were real dates not text. Note the use of the Date function
(returns system date) rather than Now (returns date & time).

Sub OnlyTodaysDate()
'Deletes rows where the value in column C is not today's date

Dim RowNdx4 As Long
Dim LastRow4 As Long

LastRow4 = Cells(Rows.Count, "C").End(xlUp).Row

For RowNdx4 = LastRow4 To 1 Step -1
If Cells(RowNdx4, "C").Value < Date Then
Rows(RowNdx4).Delete
End If
Next RowNdx4

End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"SITCFanTN" wrote in message
...
I need to convert the date to MM/DD/YYYY format from MM/DD/YY and then
delete
all rows in the current worksheet that don't have today's date in column
C.
The number of rows is variable each day. This is the code I was using,
however it does not work now and I'm not sure why, any help you can
provide
is appreciated.

Sub OnlyTodaysDate()

' Change date to read format mm/dd/yyyy

Dim RowNum As Integer
Dim NextValue As String
Dim NextDate As Date

For RowNum = 1 To Range("C1").CurrentRegion.Rows.Count
NextValue = Range("C" & RowNum).Value
NextDate = DateSerial(2000 + Mid(NextValue, 1, 1), Mid(NextValue, 2,
2), Mid(NextValue, 4, 2))
Range("C" & RowNum).NumberFormat = "mm/dd/yyyy"
Range("C" & RowNum).Value = NextDate
Next RowNum


'Deletes rows where the value in column C is not today's date

Dim RowNdx4 As Long
Dim LastRow4 As Long

LastRow4 = Cells(Rows.Count, "C").End(xlUp).Row

For RowNdx4 = LastRow4 To 1 Step -1

If Cells(RowNdx4, "C").Value = FormatDateTime(Now, vbShortDate) =
False Then
Rows(RowNdx4).Delete
End If

Next RowNdx4

End Sub