Insert Row based on cell value VBA

[mgc8cc]

All,

I want my formula to insert X amount of rows as the value in column 9 states. (E.g. if row 2 column 9 had value = 3, then I want to run the macro to insert 3 + 1 rows) Currently its putting a value in every row of column 9.

Sub InsertRow()
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row

For i = LastRow To 2 Step by - 1
A = Worksheets("Clinic & Resource Alignment").Cells(i, 9).Value
For j = 1 To A

Worksheets("Sheet1").Rows(i + 1).Select
Selection.Insert Shift:=xlDown
   
        Next
Next
Range("G2:J2").Select
Selection.AutoFill Destination:=Range("G2:J240"), Type:=xlFillDefault
Range("G2:J240").Select
   

   
Worksheets("Sheet1").Cells(1, 1).Select
End Sub

 

[mgc8cc]

All,

I want my formula to insert X amount of rows as the value in column 9 states. (E.g. if row 2 column 9 had value = 3, then I want to run the macro to insert 3 + 1 rows) Currently its putting a value in every row of column 9.



VBA Code:

Sub InsertRow()
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row

For i = LastRow To 2 Step by - 1
A = Worksheets("Sheet1").Cells(i, 9).Value
For j = 1 To A

Worksheets("Sheet1").Rows(i + 1).Select
Selection.Insert Shift:=xlDown

Next
Next
Range("G2:J2").Select
Selection.AutoFill Destination:=Range("G2:J240"), Type:=xlFillDefault
Range("G2:J240").Select



Worksheets("Sheet1").Cells(1, 1).Select
End Sub

 

[DataBlake]

i feel like this is your answer but instead of "with cells (i, 1)" it would be with "cells(i, 9)"

auto insert row based on cell value

hi guys - sorry this is second post (same title but different querry) I want a macro to automatically insert blank row(s) based on the cell values in column A. Blank rows to be inserted below the the row with that value. e.g. row -value A1 - 0 A2 - 1 A3 - 0 A4 - 2 A5 - 4 A6 - 0 and so on I...

www.mrexcel.com