Excel VBA Macro: Autofill a list in a Weekday frequency

Question

With this macro I am able to insert a new row at the bottom of a daily time-series data. The macro performs well, but even though I specified to fill the series with Weekdays and avoid weekends, it still not do so and fills with all the days of the week.

Any suggestions on what I might be missing?

Please also see the screenshot for a better understanding.

Thank a lot.

  Sub Weekday_Data_Update()

   Range("A2").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(-1, 0).Range("A1").Select
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Resize(3).Select
   Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlWeekday,  _
   Trend:=False

End sub

Example of how the macro is filling the dates wrongly

QHarr · Answer 1 · 2018-05-05T18:28:00.687

I am confused by all your up, down, across movement. To simply extend dates across a range only using weekdays would have syntax such as follows:

Option Explicit

Public Sub Test()
    With Worksheets("Sheet1")
        .Range("A2").AutoFill Destination:=.Range("A2:A7"), Type:=xlFillWeekdays
    End With
End Sub

You can construct a fill to last used cell as follows:

.Range("A2").AutoFill Destination:=.Range(.Cells(2, "A"), .Cells(.Cells.SpecialCells(xlLastCell).Row, "A")), Type:=xlFillWeekdays

score 0 · Answer 2 · answered May 05 '18 at 18:59

Just to break it down, the below code will print ONLY the Weekdays

Sub Weekday_Data_Update()


Dim startRange As Range
Dim stopRange As Range

'Specify the cell where the date starts
Set startRange = Sheets("Sheet1").Range("A2")

'Specify the cell until which you want weekdays to be displayed
Set stopRange = Sheets("Sheet1").Range("A2:A6")

startRange.Select
Selection.AutoFill Destination:=stopRange, Type:=xlFillWeekdays



End Sub

P.S: Not sure why you many range selections in your code.

score 0 · Answer 3 · answered May 05 '18 at 22:12

0

According to MSDN, Date:=xlWeekday is only applicable when you use Type:=xlChronological

So, try

Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlWeekday,  _
Trend:=False

Refer to MSDN help for further details

answered May 05 '18 at 22:12

chris neilsen

52,446
10
84
123

You might also be interested in [how to avoid Select](https://stackoverflow.com/q/10714251/445425) – chris neilsen May 05 '18 at 22:13
thanks for your comment. The syntax you proposed seems to do the job in filling the new rows with only Weekdays. The only problem using this syntax is that the adjacent cells to each date (wich are containg a formula) are filled as a series and thus not filling with the formula. Do you think this issue can be solved while still keeping your proposed syntax? – gekibul May 06 '18 at 04:29
I have no idea what your requirements are re adjacent columns. This answer addresses your Q re `.DataSeries`. You should look into the behaviour of `DataSeries` vs other methods, eg `AutoFill` to determine what's best for your situation – chris neilsen May 06 '18 at 05:01

Excel VBA Macro: Autofill a list in a Weekday frequency

Example of how the macro is filling the dates wrongly

3 Answers3