Is there a formula or macro I can run in Excel that will allow me to delete all non-dates from a col-CodePudding

Problem: In column F of a spreadsheet I have dates (xx/yy/zzz format) interspersed with random data. The data are messing up the format of my table.

Goal: Clear contents of the cells containing data, preserve the cells containing dates.

CodePudding user response：

So you don't have to bust your gut with coding, etc. you could just insert a column, run this formula across your dates ...

=IF(ISERROR(EOMONTH(A1,1)),"",A1)

... then Copy -> Paste Special -> Values back over the top and finally, remove the working column you previously added.

I hope that makes sense.

CodePudding user response：

Remove Non-Dates in a Column

Option Explicit

Sub RemoveNonDates()
    
    Const fcAddress As String = "F2"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim rg As Range
    Dim rCount As Long
    ' Reference the non empty one-column range.
    With ws.Range(fcAddress)
        Dim lcell As Range: Set lcell = .Resize(.Worksheet.Rows.Count _
            - .Row   1).Find("*", , xlFormulas, , , xlPrevious)
        If lcell Is Nothing Then Exit Sub
        rCount = lcell.Row - .Row   1
        Set rg = .Resize(rCount)
    End With
    
    Dim Data As Variant
    ' Write the values from the range to a 2D one-based one-column array.
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    Dim r As Long
    ' Replace the non-dates with 'Empty'.
    For r = 1 To rCount
        If Not IsDate(Data(r, 1)) Then
            Data(r, 1) = Empty
        End If
    Next r
    
    ' Write the values from the array to the range.
    rg.Value = Data
    
End Sub