Autofill dates for specified year

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,074
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
Can you explain what the different pieces of the code does. Thanks
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.:oops:

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top