• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto fill date with condition

Karanbir Soin

New Member
I am maintaining a journal in which data is logged on daily basis. In the first column [A] I want to auto fill with date starting from the first date in A5. The auto fill with date as series does the fill. However the dates for the weekends that is Saturday Sunday is not required. How to remove these dates from the entire fill?
best Regards…
 

AlanSidman

Well-Known Member
Here is a VBA solution that will fill as you requested

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
    Dim i As Long, lr As Long
    lr = Range(cn & Rows.Count).End(xlUp).Row
    For i = lr To sn Step -1
    If Weekday(Range(cn & i)) = 1 Or Weekday(Range(cn & i)) = 7 Then
    Range(cn & i).EntireRow.Delete
    End If
    Next i
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub
 

AlanSidman

Well-Known Member
This should help you

How to install your new code
  • Copy the Excel VBA code
  • Select the workbook in which you want to store the Excel VBA code
  • Press Alt+F11 to open the Visual Basic Editor
  • Choose Insert > Module
  • Edit > Paste the macro into the module that appeared
  • Close the VBEditor
  • Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
  • Press Alt-F8 to open the macro list
  • Select a macro in the list
  • Click the Run button
 

Peter Bartholomew

Well-Known Member
This is applicable to Excel 365 only.
Code:
= LET(
    listDates, SEQUENCE(daysElapsed,1,initialDate),
    working?,  WEEKDAY(listDates,11) < 6,
    FILTER(listDates, working?) )
With 365 insider beta, this can be taken further by using a LAMBDA function to wrap the LET function giving,
= CALENDAR(initialDate, daysElapsed)
where CALENDAR is the defined Name given to the function.
 

AlanSidman

Well-Known Member
@Peter
Can you post a sample file. I am trying to get your formula to work. I have never worked with the LET function and would like to see it in action.

Alan
 

Peter Bartholomew

Well-Known Member
Hi Alan, I am relieved you find the solution of interest. It sometimes concerns me, when I am following up my own interests relating to the changes made possible by dynamic arrays, that I cause offense to others.
 

Attachments

Top