Owl
11-12-2020, 05:01 PM
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
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