Add date in blank cells using vba

[MPFraser7]

I currently use the following code to add the date "31/12/9998" in blank cells of column F. The problem I have is after running the code, my filters are now on a "A to Z" rather than "Newest to oldest". How do I go about formatting the column in order to be able to sort by Newest to Oldest?

Thanks!

Columns("F:F").Select
Selection.SpecialCells(xlCellTypeBlanks).dateValue = "31/12/9998"
Selection.NumberFormat = "dd/mm/yyyy h:mm"

 

[Joe4]

Try this:

Columns("F:F").SpecialCells(xlCellTypeBlanks) = DateSerial(9998, 12, 31)
Columns("F:F").NumberFormat = "dd/mm/yyyy h:mm"

 

[MPFraser7]

I always seem to overthink it... Thank you!

 

[Joe4]

You are welcome!

I think the key was to enter it as a date, and not text.