Insert variable number of rows for every row in spreadsheet

[Busybeaver]

Afternoon and Happy New Year to all

Can someone please help me with a code for the below. What I'm trying to achieve is to insert rows below each line based on the number of lines required in the last column. I.e. No rows need to be inserted for the first 2 lines but 2 additional rows need to be inserted for the 3rd line.

I have tried so many codes that I have found online but can only find codes that will insert either only 1 row at a fixed range or will only insert rows once.

Unfortunately I don't have the knowledge to attempt to write a code like this by myself but would greatly appreciate any help from someone who does.

CRM	Task Open	Category	Address	Suburb	DEL/REM	Bin type	Bulk #	PID #	Total line Count
212226/2020	21-Dec-20​	***	***	***	DEL	FOOD			0
211640/2020	21-Dec-20​	***	***	***	DEL	FOOD			0
212343/2020	22-Dec-20​	***	***	***	DEL	FOOD	3		2
211704/2020	21-Dec-20​	***	***	***	DEL	FOOD / GLASS			1
206386/2020	21-Dec-20​	***	***	***	DEL	RUBBISH / RECYCLING / GLASS	2		5
212350/2020	22-Dec-20​	***	***	***	DEL/REPAIR	RUBBISH / FOOD			1

 

[My Aswer Is This]

Is Total line Count
Column J ??

 

[Busybeaver]

Hi, yes. Total count is in column J

 

[My Aswer Is This]

Try this:

Sub Insert_Rows()
'Modified 12/31/2020  10:42:57 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As Long
Lastrow = Cells(Rows.Count, "J").End(xlUp).Row

For i = Lastrow To 1 Step -1
    ans = Cells(i, "J").Value
        If ans > 0 Then Rows(i).Offset(1).Resize(ans).Insert
Next
Application.ScreenUpdating = True
End Sub

 

[Busybeaver]

Works perfectly! Thank-you so much.

 

[My Aswer Is This]

Works perfectly! Thank-you so much.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.