+ Reply to Thread
Results 1 to 2 of 2

Insert rows based on cell value then auto fill blanks

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    belfast
    MS-Off Ver
    Excel 2010
    Posts
    12

    Insert rows based on cell value then auto fill blanks

    Hi guys,

    I was wondering if someone could help me out with this,

    I am trying to tidy up some price files but it is taking forever to do as i have hundreds of thousands of rows to do, so i was looking at some ways to speed it up a bit.

    Basically in any given row, i have a part for a motorbike, in one column, there is a year range, for example, 98-04, (this can range from 1950-2018) there is also cost, retail price, part number etc,

    So far i have a vba to insert rows based on a cell value i have manually worked out from the year range 98-04 so in cell A1 for example i type the number 6, so when i run the vba it adds 6 rows below A1, which also takes a long time

    So in an ideal world, i would like to have a vba that will do the following to the attached file:

    -look at cell C1, which contains 95-01, i need it to add 6 rows below ( for 1996,1997,1998,1999,2000 and 2001) as the original row will be the 1995 row, and if possible fill in these year values iin their respective rows ie, 1995 in C1, 1996 in C2, 1997 in C3 etc

    -and then once that is done if it could fill in all the blanks that are created with exactly what is in the respective rows above excluding the year column, so the example above, there will be 7 identical rows with the only difference being the year.

    I have tried to do this with highlighting a column pressing F5,alt+s, alt+k, then ctrl+enter, however if there is a blank cell, for example I10 is supposed to be blank, so i dont want it to be filled with the value from I9

    I think that's it if its possible to do, the following is the vba i have been using to start with (for this to work a new A column needs to be added to the attached file with the number of rows to insert,

    Anyone's help would be greatly appreciated,

    thanks in advance

    regards,

    Warren.

    ************************************************************************************

    Public Sub Insert()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual 'pre XL97 xlManual

    lastrow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Cells(lastrow, 1).Select

    Set CurrentCell = ActiveSheet.Cells(lastrow, 1)

    For n = lastrow To 0 Step -1
    If n = lastrow Then GoTo CheckLastRow
    If n = 1 Then GoTo CheckfirstRow
    ActiveCell.Offset(-2, 0).Select

    CheckLastRow:
    Set NextCell = CurrentCell.Offset(-1, 0)

    ActiveCell.Offset(1, 0).Select
    For i = 1 To CurrentCell
    ActiveCell.EntireRow.Insert
    Next i

    Set CurrentCell = NextCell
    Next n

    'To be performed on the firstrow in the column
    CheckfirstRow:
    ActiveCell.Offset(-1, 0).Select
    For i = 1 To CurrentCell
    ActiveCell.EntireRow.Insert
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

    ******************************************************************************
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Insert rows based on cell value then auto fill blanks

    Not sure I fully understand what you are after, but give this a try. It assumes the user selects a starting cell for the year and that will be the row to copy. It asks for the number of rows to insert, which will exclude the starting row. It then asks for the year for the starting cell. The rest is automatic.
    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 09-02-2017 at 10:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto Fill Row based on value and auto delete shift cell up based on value too
    By morr1 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-14-2017, 07:12 PM
  2. Insert rows based on count and fill rows with adjacent values
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-19-2015, 02:57 PM
  3. Insert rows based on a cell value and Fill down column A
    By blaxor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2014, 04:54 AM
  4. [SOLVED] Insert and Delete blanks rows so that there are 2 blanks rows between text rows
    By erniedawg in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-29-2013, 02:27 AM
  5. [SOLVED] VBA Macro - Insert Rows based on different Column Value but Ignoring Blanks Cells
    By WITJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2013, 03:11 PM
  6. Macro to auto fill rows based on one cell value
    By jennieS in forum Excel General
    Replies: 6
    Last Post: 08-18-2010, 03:10 PM
  7. Replies: 3
    Last Post: 01-14-2006, 08:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1