VBA Help - Auto generate multiple rows base on cell value

[Owl]

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

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!!! :banghead:

'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

[jolivanes]

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

[Owl]

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.
27441

[JimmyTheHand]

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")