Autofill dates for specified year

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
743
Can someone assist with an Autofill code that autofill dates in column A for a specified year (i.e. 01/01/2019 - 12/31/2019)

Thank you kindly
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,761
Office Version
365
Platform
Windows
If you have the first date in cell A1, then try

Rich (BB code):
Sub FillDates()
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, Step:=1, Stop:=DateSerial(Year(Range("A1").Value), 12, 31)
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,761
Office Version
365
Platform
Windows
Can you explain what the different pieces of the code does. Thanks
It just uses one of Excel's built-in features.
DataSeries We want some sort of series (pattern)
Rowcol:=xlColumns Go down columns not across rows.
Type:=xlChronological We want some sort of date or time pattern
Date:=xlDay The date pattern is related to days, not months or years or just weekdays
Step:=1 Going up 1 day at a time
Stop:=DateSerial(Year(Range("A1").Value), 12, 31) Stop when you get to the end of the year


To investigate further, if you put that first date (or any number) in a fresh sheet, select that cell with the first date or number then go
Home ribbon tab -> Fill (drop-down) in the Editing group -> Series ..
and try different options in that dialog.
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,103
Office Version
2019
Platform
Windows
Here is an alternative solution:

Code:
Option Explicit


Sub AutoDateFill()
    Dim x As Date
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    
    
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")


    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting Date? mm/dd/yyyy")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,576
Office Version
2010
Platform
Windows
Here is alternate one-liner that you can consider (assumes as Peter did that the starting date is in cell A1)...
Code:
Sub FillDates()
  [A1].Resize(DateAdd("yyyy", 1, [A1]) - [A1]) = [A1+ROW(1:366)-1]
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,761
Office Version
365
Platform
Windows
Here is alternate one-liner that you can consider (assumes as Peter did that the starting date is in cell A1)...
It also assumes that the column is already formatted as Date?
The Fill Series does not require that assumption.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,576
Office Version
2010
Platform
Windows
It also assumes that the column is already formatted as Date?
I missed that as I had run your code beforehand to see exactly what was being placed in the cells and doing that changed the cells to Date format.:banghead:

I can save my one-liner with the modified code below, but it does require the programmer to know the date format being used in cell A1 so that he/she can set the correct date format pattern which I have highlighted in red.
Code:
Sub FillDates()
  [A1].Resize(DateAdd("yyyy", 1, [A1]) - [A1]) = [IF({1},TEXT(A1+ROW(1:366)-1,"[B][COLOR="#FF0000"]d/m/yyyy[/COLOR][/B]"))]
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,761
Office Version
365
Platform
Windows
I can save my one-liner ..
I'm not so sure...
That does some really strange things for me.
I started out with the whole column formatted as General & my date system is d/m/y
When I enter 1/1/2019 in A1 it shows up as 1/01/2019 & the format of that cell changes to Date & if I delve into the detail that format is d/mm/yyyy (as expected)

After then running your code I get what you see below!!
As you can tell from the left/right alignment, I have 12 dates (which are in fact 1 Jan, 1 Feb, 1 Mar, ...) & the cell formats change to Date
& then 19 rows of text values which aren't dates at all & the cells remain formatted as General
& then 12 dates (2 Jan, 2 Feb, 2 Mar etc) formatted as Date
& then 16 rows of text values (General)
& then ...

Fill Series is looking much simpler & more robust to me. :eek:


Excel Workbook
A
11/01/2019
21/02/2019
31/03/2019
41/04/2019
51/05/2019
61/06/2019
71/07/2019
81/08/2019
91/09/2019
101/10/2019
111/11/2019
121/12/2019
1313/1/2019
1414/1/2019
1515/1/2019
1616/1/2019
1717/1/2019
1818/1/2019
1919/1/2019
2020/1/2019
2121/1/2019
2222/1/2019
2323/1/2019
2424/1/2019
2525/1/2019
2626/1/2019
2727/1/2019
2828/1/2019
2929/1/2019
3030/1/2019
3131/1/2019
322/01/2019
332/02/2019
342/03/2019
352/04/2019
362/05/2019
372/06/2019
382/07/2019
392/08/2019
402/09/2019
412/10/2019
422/11/2019
432/12/2019
4413/2/2019
4514/2/2019
Fill Series
 

Forum statistics

Threads
1,086,138
Messages
5,388,055
Members
402,099
Latest member
The OB1s and 2s

Some videos you may like

This Week's Hot Topics

Top