VBA - Replace each date cell in range with its corresponding year

[RockandGrohl]

I'm doing this at the moment, but it's just a messy loop for simpletons:

    Do Until Cells(ActiveCell.Row, "A").Value = ""
        If ActiveCell.Value <> "" Then
        ActiveCell.Value = Year(ActiveCell.Value)
        ActiveCell.NumberFormat = general
        End If
    ActiveCell.Offset(1, 0).Activate
    Loop

It's just to replace a column (that may or may not have blanks) that contains dates with the year. I can't format because the date will still exist as "04/10/2020" - it needs to just be an integer of "2020"

Is there a better way of doing it? Takes a few seconds to run the loop but I'm sure it can be instant. Thanks.

 

[Peter_SSs]

Assuming you are starting at row 2 and the column contains only dates and blank cells, try this with a copy of your data.

Sub Year_Only()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .NumberFormat = "General"
    .Value = Evaluate(Replace("if(#="""","""",year(#))", "#", .Address))
  End With
End Sub

 

[RockandGrohl]

Thanks, I'll give this a whirl in a couple hours, this is what I wanted to do but couldn't remember the syntax (although the Evaluate is new to me!)

 

[RockandGrohl]

Worked perfectly, thanks!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.