Autofill dates for specified year

[Av8tordude]

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]

If you have the first date in cell A1, then try

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

 

[Av8tordude]

Thank you Peter

 

[Av8tordude]

Can you explain what the different pieces of the code does. Thanks

 

[Peter_SSs]

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.

 

[alansidman]

Here is an alternative solution:

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]

Here is alternate one-liner that you can consider (assumes as Peter did that the starting date is in cell A1)...

Sub FillDates()
  [A1].Resize(DateAdd("yyyy", 1, [A1]) - [A1]) = [A1+ROW(1:366)-1]
End Sub

 

[Peter_SSs]

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]

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.

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.

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]

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.

Excel Workbook
	A
1	1/01/2019
2	1/02/2019
3	1/03/2019
4	1/04/2019
5	1/05/2019
6	1/06/2019
7	1/07/2019
8	1/08/2019
9	1/09/2019
10	1/10/2019
11	1/11/2019
12	1/12/2019
13	13/1/2019
14	14/1/2019
15	15/1/2019
16	16/1/2019
17	17/1/2019
18	18/1/2019
19	19/1/2019
20	20/1/2019
21	21/1/2019
22	22/1/2019
23	23/1/2019
24	24/1/2019
25	25/1/2019
26	26/1/2019
27	27/1/2019
28	28/1/2019
29	29/1/2019
30	30/1/2019
31	31/1/2019
32	2/01/2019
33	2/02/2019
34	2/03/2019
35	2/04/2019
36	2/05/2019
37	2/06/2019
38	2/07/2019
39	2/08/2019
40	2/09/2019
41	2/10/2019
42	2/11/2019
43	2/12/2019
44	13/2/2019
45	14/2/2019
Fill Series