VBA to add rows between dates and populate other columns

  • Hi. I would appreciate some help with this. I not very familiar with VBA, but have copied some codes from time to time and modified them slightly with some good results. So, if you can point me to the right direction that would be great.

    I receive monthly data that has random dates in it and other data, with multiple columns. Simplified example:

    | Event Date | Events |
    | 28/11/2022 | 10     |
    | 13/10/2022 | 5      |
    | 23/02/2022 | 8      |
    | 12/11/2021 | 9      |

    This goes on to 2019 with many missing dates, exemplified above, as a date is only added when something happens. So, I can't change the source.

    As the souce can't be changed, I thought a VBA code might help. One that adds the missing monthly dates (one row per missing month, perhaps as the first of the month) to that spreadsheet, in the correct order. So, it would add the rows between 23/02/2022 and 13/10/2022 and between 12/11/2022 and 23/02/2022. Then, it needs to add a zero to particular columns to match the real data.

    The spreadsheet will come fresh every month. So, it needs to identify that the dataset will increase in the number of rows every month, or not. More info if it helps:

    • It goes from column A to BR.
    • The date column is in E. Starts in E2, with name Event Date in the format dd/mm/yyyy.
    • Column G has the Event Month column that is formatted to a number, 1 to 12, according to the date in E. So, this column G needs to display the month of whatever row being inserted, in that format mm.
    • Column H is Event Year and should also display the year of the new date row, in format yyyy.
    • Column A to D could display 0 (zero) I think and L to BR, display 0 (zero) for the new rows. They have very specific data, but hopefully if they have zero, it's fine. So, it needs to add a zero in all those columns that are not date specific.

    I found this code that is adding rows to the end of the spreadsheet but not between the dates.

    and this one for adding the zeros in the cells

    Range("A1").Value = "0"

    But how can I tell it to to create the rows between missing dates and add the zero in many specific columns?

    Any help is appreciated. Thank you.

  • Hello and Welcome to the Forum :)

    Best thing to do ... in order to increase your chances of getting a tailor-made solution ... attach a sample file ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!