Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |