Consulting

Results 1 to 4 of 4

Thread: VBA Help - Auto generate multiple rows base on cell value

  1. #1
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location

    VBA Help - Auto generate multiple rows base on cell value

    Hi all, please help me as i am new on vba.

    I need the macro to auto generate multiple rows base on cell value (Column N)

    let's say the value at cell N20 is 4, the macro will auto generate 4 rows above N20.
    If the value at cell N30 is 16, the macro will auto generate 16 rows above N30.

    Below is the code i used, it only works for creating 1 row (even though the cell value is more than 1)and I have no idea how to create multiple rows. Please help!!!

    'Insert blank rows by number of missing rows
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Set WorkRng = WorkRng.Columns(1)
    xLastRow = WorkRng.Rows.Count
    Application.ScreenUpdating = False
    For xRowIndex = xLastRow To 1 Step -1
    Set Rng = WorkRng.Range("A" & xRowIndex)
    If Rng.Value > 1 Then
    Rng.EntireRow.Insert Shift:=xlDown
    End If
    Next
    Application.ScreenUpdating = True


    End Sub

  2. #2
    Select the Cell in Column "N" that has the number in it and run this.
    Sub Maybe()
    If Selection.Value > Selection.Row Then MsgBox "Your selected cell value is to high.": Exit Sub
    Selection.Offset(-(Selection.Value)).Resize(Selection.Value).EntireRow.Insert Shift:=xlDown
    End Sub

  3. #3
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Hi Jolivanes, thanks for helping! but unfortunately there is an error.

    The macro will copy the whole data to a new worksheet and add some formulas which resulted in column "N".
    I am unable to highlight "N" right from the start.

    I need to macro to auto generate the number of rows base on column "N". those figures in negative can be ignored.
    Eg. column N is 5, then auto generate 4 rows. If is 0 or 1, don't need to generate any rows.

  4. #4
    Try this code:
    Sub AutoGenerateRows_BasedOnColumnN(TargetSheet As Worksheet)
        Dim rng As Range, c As Range, i As Long
       
        Set rng = Intersect(TargetSheet.UsedRange, TargetSheet.Range("N:N"))
        For i = rng.Cells.Count To 1 Step -1
            Set c = rng(i)
            If c.Value > 1 Then
                c.EntireRow.Resize(c.Value - 1).Insert shift:=xlShiftDown
            End If
        Next
    End Sub
    You can call the above sub like this:
    AutoGenerateRows_BasedOnColumnN TargetSheet:=ThisWorkbook.Worksheets("Sheet1")
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

Posting Permissions

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