INSERT Number of Rows based on Cell Value and populate rows below with initial value

  • Hi there


    I have a spreadsheet with the following data


    COL A | COL B | COL C

    101.0001| 18 | abc

    101.0020 | 4 | def

    101.0025 00| 74 | ghi

    101.0100 etc. etc.


    102.0001 | 2 | jkl


    102.0004 etc


    Column B is just the difference in values between column A1 and Column A2 - 1. This gives the number of rows to insert between each row.


    I can use the following code to insert the correct number of rows between each:



    However, what I need to do is populate all of the new "in between" rows with the value in the initial row so that rows 1 to 10 are populated with the value 101.0001 and the new rows after 101.0020 are populated with 101.0020 and the new rows after 101.0025 are populated with 101.0025 etc etc.


    Also, if possible, I would like to populate column C with the starting values consequtively +1 so that ultimately after the macro is run the table would look like:


    COL A | COL B | COL C

    101.0001| 18 | 101.0001

    101.0001| | 101.0002

    101.0001| | 101.0003

    101.0001| | 101.0004

    101.0001| | 101.0005

    101.0001| | 101.0006

    101.0001| | 101.0007

    101.0001| | 101.0008

    101.0001| | 101.0009

    101.0001| | 101.0010

    101.0001| | 101.0011

    101.0001| | 101.0012

    101.0001| | 101.0013

    101.0001| | 101.0014

    101.0001| | 101.0015

    101.0001| | 101.0016

    101.0001| | 101.0017

    101.0001| | 101.0018

    101.0001| | 101.0019

    101.0020 | 4 | 101.0020

    101.0020 | | 101.0021

    101.0020 | | 101.0022

    101.0020 | | 101.0023

    101.0020 | | 101.0024

    102.0001 | 2 | 102.0001

    102.0001 | | 102.0002

    102.0001 | | 102.0003

    etc. etc.


    Note that what is in Column C is not purely consequtive. There may be a reasonably easy Excel formula that may be dragged down to generate what is in column C from Column A but it it can all be done with a Macro that would be great.


    What is in Column A is an underlying *.PDF file name and what is in colum C is the page number in that PDF.


    I hope that makes sense.


    Any help is much appreciated!