locked
auto pop. rows RRS feed

  • Question

  • I want to auto populate rows according to the number I type in a referenced cell. So if I type in 10, I need ten rows to auto pop that also say "DI" in them
    Monday, August 8, 2016 7:38 PM

Answers

  • Hi

    Try this one.

    Sub InsertRow_Inputbox()
        Dim xrows As Long, Y As Integer, X As Integer
        Application.ScreenUpdating = False
        X = InputBox("How many rows would you like")
        Y = InputBox("At what row to install them. E.g. Type just a number")
        For xrows = 1 To X
            Cells(Y, 1).EntireRow.Insert Shift:=xlDown
            Cells(Y, 1).Value = "DI"
        Next xrows

        Application.ScreenUpdating = True
    End Sub


    Cimjet

    Tuesday, August 9, 2016 12:36 AM

All replies

  • Hi

    Try this one.

    Sub InsertRow_Inputbox()
        Dim xrows As Long, Y As Integer, X As Integer
        Application.ScreenUpdating = False
        X = InputBox("How many rows would you like")
        Y = InputBox("At what row to install them. E.g. Type just a number")
        For xrows = 1 To X
            Cells(Y, 1).EntireRow.Insert Shift:=xlDown
            Cells(Y, 1).Value = "DI"
        Next xrows

        Application.ScreenUpdating = True
    End Sub


    Cimjet

    Tuesday, August 9, 2016 12:36 AM
  • I want to auto populate rows according to the number I type in a referenced cell. So if I type in 10, I need ten rows to auto pop that also say "DI" in them

    example

    Col A 
    5
    3
    2
    Col B
    DI
    DI
    DI
    DI
    DI
    DI
    DI
    DI
    DI
    DI

    Tuesday, August 9, 2016 7:45 PM
  • I want to insert rows into cell b based on the value in cell a. The inserted rows also need to be filled with DI

    Option Explicit

    Sub Copy_N_Times() Dim lr AsLong'last row col. ADim nr AsLong'row counter col. BDim x AsLong'generic counter lr = Range("A" & Rows.Count).End(xlUp).Row nr = 1 For x = 1 To lr Range("B" & nr).Resize(Range("A" & x).Value) = "DI" nr = nr + 1 + Range("A" & x).Value Next x End Sub

    Now how would I change it if the numbers started in a specific cell and row D6
    and I need the extra rows and "DI" inserted into sheet "Equipment IO" starting at D4?

    ol

    Tuesday, August 9, 2016 9:44 PM
  • Hi mwareing,

    I find that you had already created a thread for the same issue and on that thread the another community member suggested a solution for that.

    I test that solution and find that it is working as per your requirement.

    if you don't want inputbox and you want to take a value from cell then just make a change in that line.

    if you have any problem with that solution then let us know.

    to avoid the duplication of the threads I merge these two threads and make it one.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 10, 2016 12:26 AM
  • Hi mwareing,

    I again find that you had created another duplicate thread for the same issue.

    if you have further question in the same issue then you can continue in that same thread instead of creating a new thread for the same issue.

    you can create new thread for every new Question. but here I find that your issue is a same just you have some additional requirements in that.

    so here again I need to merge this thread with old one to avoid the duplication.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 10, 2016 12:44 AM
  • Hi mwareing,

    you had asked,"Now how would I change it if the numbers started in a specific cell and row  D6
    and I need the extra rows and "DI" inserted into sheet "Equipment IO" starting at D4?"

    as I already recommend you to check the suggestion given by another community member.

    in that suggestion you will find the code to start the number in specific cell.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 10, 2016 12:52 AM
  • Sorry I didn't see your replies. 

    I have a new question, let me know if I should start a new thread

    Now that I am able to populate one column in two rows according to the cell value, I need to populate the adjacent column and have those rows say DI and come after the "DO"s of the adjacent column

    worksheet ("scope")

    D6 E6


    3 1
    1 6
    2 3


    worksheet ("Equipment IO")

    D4

    DI
    DI
    DI
    DO
    DI
    DO
    DO
    DO
    DO
    DO
    DO
    DI
    DI
    DO
    DO
    DO




    ol

    Wednesday, August 10, 2016 9:15 PM
  • Hi mwareing,

    the issue is same so you can continue in this thread.

    to deal with adjacent rows and column you need to use Offset.

    From your above description of desired output I can see that there are some "DL" and "DO" in the column but I can't understand when to insert "DO" in column and for how many times repeat this step.

    so here I share you a link of Offset and you can try to look in to that and as per your requirement you can add the "DO" in column with the help of Offset.

    Range.Offset Property (Excel)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 11, 2016 12:17 AM