Declaring a variable with a date found in sheet, i

Question

Currently I am trying to declare a variable as a date I pulled from a worksheet in excel, then use that declared date as a benchmark so I can delete everything greater than it. I think my code is solid for this task but the issue I keep having is when I look at what my date is declared as instead of being 1/12/2015 as it should it comes up at 12:00:00am and it replaces the contents of the cell in which I pulled the date from. Here is my code so far...

Sub Remove_Unecessary_Data_1()

Dim ALLCs As Worksheet
Dim DS As Worksheet
Dim Dailystring As String
Dim Blank As String
Dim finaldate As Date


Dailystring = " - Daily"
Blank = ""

Set DS = Sheets("Data Summary")
Set ALLCs = Sheets("Asset LLC (Input)")



    ALLCs.Select
        For y = 1 To 40
           If InStr(1, Allcs.Cells(13, y), "Timestamp of Execution") Then
                finaldate = Allcs.Cells(50, y)
            End If
        Next

    ALLCs.Select
        For u = 1 To 40
            If InStr(1, Allcs.Cells(13, u), "Start Date") Then
                For p = 2 To 69584
                If allcs.Cells(p + 14, u) > finaldate Then
                allcs.Cells(p + 14, u).EntireRow.Delete
                End If
                Next
            End If
        Next

I think the main issue herein lies that finaldate is being transforming into that odd time. Any ideas?

Also for reference the cell its pulling from 50,y reads

1/12/2015.

EDIT: I updated my code to reflect @freemans commented suggestion on the placement of my finaldate when trying to assign it a value. I had it reversed.

My new problem is the second part of my code doesn't accurately remove all of the dates necessary. For instance I need all of the dates about 1/12/15 removed, my program still does not do this, thought I don't see the error in my code

        ALLCs.Select
        For u = 1 To 40
            If InStr(1, Allcs.Cells(13, u), "Start Date") Then
                For p = 2 To 69584
                If allcs.Cells(p + 14, u) > finaldate Then
                allcs.Cells(p + 14, u).EntireRow.Delete
                End If
                Next
            End If
        Next

My issue lies here, why would 1/13/15 not be deleted if finaldate is declared as 1/12/15.

Answer 1

You declare

Dim finaldate As Date

and you use it here

Cells(50, y) = finaldate

and here

            If Cells(p + 14, u) > finaldate Then
            Cells(p + 14, u).EntireRow.Delete
            End If

But you've never assigned a value to finaldate, therefore you're filling in a date/time of 0. Any date value in Cells([+14,u) is highly likely to be greater than zero, therefore the row will be deleted.

Based on the comments below, try this updated code:

Sub Remove_Unecessary_Data_1()

Dim ALLCs As Worksheet
Dim DS As Worksheet
Dim Dailystring As String
Dim Blank As String
Dim finaldate As Date

Dailystring = " - Daily"
Blank = ""

'----------------------------Note this line:
finaldate = 'something! Today? Now? some user specified date? you don't tell us
'----------------------------

Set DS = Sheets("Data Summary")
Set ALLCs = Sheets("Asset LLC (Input)")

'ALLCs.Select     'to remove the possibility of future ambiguity, make the change below
For y = 1 To 40
    If InStr(1, AllCs.Cells(13, y), "Timestamp of Execution") Then
'note, you assign TO the left side of the equal sign (=) FROM the right side
        finaldate = AllCs.Cells(50, y)
    End If
Next

'ALLCs.Select
For u = 1 To 40
    If InStr(1, AllCs.Cells(13, u), "Start Date") Then
        For p = 2 To 69584
          If AllCs.Cells(p + 14, u) > finaldate Then
            AllCs.Cells(p + 14, u).EntireRow.Delete
          End If
        Next
    End If
Next