Autopopulate Dates for a certain Range

[oasisdec]

Hello,

I am stuck on how to get this task done. I need to fill in the date automatically across the rest of the table by finding the last blank cell in the row, referencing the the cell before and filling the table to the last column. So in the example below F1 has a date and I need to fill in G1:L1 with dates (10/15/2017 - 10/20/2017). The tricky part is that the last date may be in any one of the columns, however the data falls. However the amount of columns in the table is set.

For simplicity's sake I only am referring to this smaller sample, in reality the table goes out to Column IV.

[TABLE="width: 500"]
<tbody>[TR]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]10/14/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for your helpl!

 

[Peter_SSs]

That is pretty light on detail, but see if this does what you want. If not please provide more detail/examples.

Sub Fill_Dates()
  Dim lc As Long
  
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  If lc < 12 Then  '(12 = Column L)
    Cells(1, lc).Resize(, 12 - lc + 1).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlDay, Step:=1, Trend:=False
  End If
End Sub

 

[oasisdec]

Peter_SSs,

Thank you! That does exactly what I needed. I replaced "12" with "256" and the date populated all the way to column IV.

I am trying to understand the code so that I can improve. Can you please tell me why it is necessary to have the ".resize"? I took it out and of course it didn't work anymore!

 

[Peter_SSs]

Thank you! That does exactly what I needed.

Good to hear.

Can you please tell me why it is necessary to have the ".resize"? I took it out and of course it didn't work anymore!

No, it won't work without that. lc is the last column that already has a date. That is 6 (column F) in your sample above. So Cell(1,lc) = cell F1 (row 1, column 6). Unless we tell it, Excel does not know how many cells to fill with the dates and the Resize does that.

In the example, remembering that lc=6
Resize(, 12 - lc + 1) becomes Resize(, 12 - 6 + 1) which is Resize(, 7)
Starting at F1 (where your date was) and selecting to the right until you have 7 cells selected, you will have selected F1:L1 which is where you originally said you wanted to fill the dates to:

F1 has a date and I need to fill in G1:L1 with dates