Hi all,
I'm trying to insert multiple rows in a spreadsheet. The number of rows inserted is based on the cell value - 1.
I'm able to change the number of rows inserted, but the number if fixed. I want the number to change depending on the value in column B.
Thanks,
Sub InsertRows()
Dim lastCll As Long
Dim cllValu As Long
Dim i As Long
Dim y As Long
Dim x As String
Dim Rnum As Long
x = "Select"
y = 1
Rnum = cllValu - 1
' find last used cell in Column B
lastCll = Cells(Rows.Count, "B").End(xlUp).Row
' get value of last used cell in column B
cllValu = Cells(lastCll, 2).Value
' insert rows by looping from bottom
For i = lastCll To 2 Step -1
' if cell value is not equal to x insert a row
If Cells(i, 1).Value <> x Then
' Next cell value
cllValu = Cells(i, 2).Value
Rows(i + 1).Resize(2).Insert
' Resize (.Value - 1)
End If
Next i
End Sub
Column A |
Column B |
Name |
Value |
456 |
1 |
1 |
454 |
1 |
Select |
3 |
158 |
1 |
Select |
1 |
865 |
1 |
546 |
2 |
948 |
3 |
Select |
4 |
556 |
1 |
642 |
2 |
Select |
3 |
545 |
1 |
Bookmarks