+ Reply to Thread
Results 1 to 3 of 3

Insert row automatically with correct format and drop down list

Hybrid View

exclaymation Insert row automatically with... 05-20-2013, 12:39 AM
ragulduy Re: Insert row automatically... 05-20-2013, 04:48 AM
gaikwad.mm@gmail.com Re: Insert row automatically... 05-20-2013, 04:56 AM
  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Insert row automatically with correct format and drop down list

    Hey,

    I am currently working on a spreadsheet which I have to add 2 rows every 44th row.

    I would like to insert 2 rows between rows 29 and 30. The first row has to contain data but the second added row is meant to stay blank (although it would require to maintain the formatting)

    On the first row to be added, the data is
    Column A - Blank
    Column B - Fatal Issue (Yes/No)
    Column C - Drop down list linked to sheet 1 (called '1. Criteria', the cells in the list are B66 and B67)
    Column D - If Yes, explain it in the Comments section

    I need these rows inserted every 44th row (so, the first two rows will be inserted between rows 29 and 30, the second two rows will be inserted between 73 and 74 the third between 117 and 118 and so on for about 5000 rows but I will have to add more data later on, so it will probably get up to 10000).

    I assume a macro could do this, but I have zero skills. Could anyone help me out with this issue? ANy help would be greatly appreciated.
    Last edited by exclaymation; 05-20-2013 at 12:52 AM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Insert row automatically with correct format and drop down list

    I don't think you cover what you want the formatting to look like but if you have the formatt and drop downs in some other cells (maybe hidden somewhere) you could copy and paste them. To take it in steps:

    To copy and insert cells:
    sub Copy_and_Insert()
    Rows("1:2").copy
    Rows("30").insert xldown
    end sub
    You would need to create a loop of some kind in order to repeat the code, to do this you will need to set a count variable as the row number to insert and increment this, for example:
    
    sub Copy_and_Insert()
    dim count
    count = 30
    Rows("1:2").copy
    Rows(count).insert xldown
    end sub
    Finally, you need to decide on the type of loop - if you know how many times you need to do it, you could use a for loop such as this:

    Sub Copy_and_Insert()
    Dim row_count, loop_count
    row_count = 30
    For loop_count = 1 To 500
        Rows("1:2").Copy
        Rows(row_count).Insert xlDown
        row_count = row_count + 46
    Next
    End Sub
    Note - I have assumed that you mean every 44th row as it is before running the macro, so once 2 rows are inserted between 29 and 30, the next two should be between 75 and 76, hence the 46 increment

    Alternatively, if you don't know how many times you want to run the loop but there is a condition that will change when you want to stop running the loop (for example, there is no more data) you could use a do...until loop:
    Sub Copy_and_Insert()
    Dim row_count
    row_count = 30
    Do Until Range("A" & row_count) = ""
        Rows("1:2").Copy
        Rows(row_count).Insert xlDown
        row_count = row_count + 46
    Loop
    End Sub

  3. #3
    Forum Contributor
    Join Date
    12-28-2012
    Location
    Mumbai - India
    MS-Off Ver
    Excel
    Posts
    145

    Re: Insert row automatically with correct format and drop down list

    Hi - Try this.., It is working fine
    Line insertion will start from the selection CELL


    Sub Insert_Blank_Rows()
    On Error GoTo ExitLoop
         'Select last row in worksheet.
        Selection.End(xlDown).Select
         
        Do Until ActiveCell.Row = 1
            ActiveCell.EntireRow.Insert shift:=xlDown
            ActiveCell.Offset(-44, 0).Select 'Insert @ 44th rows [change as per requirement]
        Loop
    ExitLoop:
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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