VBA code to insert rows based on value. Current code breaking on zero

[davie_aus]

Hey all,
Ive found some code on a forum elsewhere and tried to retrofit it solve a problem im facing with formatting.

I need to insert a certain amount of blank rows based on a value. I.E: If the value is 3, i need to insert 3 blank rows below this cell. The code ive found works nicely, but when it gets to 0 it doesnt work. Can anyone cast your eye over it and let me know if you find anything that may help.

Current Code:

Sub InsertRowsIf()
Dim lr As Long, R As Range, i As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
Set R = Range("C1", "C" & lr)

Application.ScreenUpdating = False
For i = R.Rows.Count To 1 Step -1

If IsNumeric(R.Cells(i, 1).Value) And Not IsEmpty(R.Cells(i, 1)) Then
R.Cells(i, 1).Offset(1, 0).Resize(R.Cells(i, 1).Value).EntireRow.Insert

End If
Next
End Sub

Input:

Day/Night Shift	Date Code	Value
Day	1/1/2018	0
Day	1/1/2018	1
Night	1/1/2018	0
Night	1/1/2018	0
Night	1/1/2018	0
Day	1/2/2018	2
Night	1/2/2018	0
Night	1/2/2018	1
Day	1/3/2018	3
Night	1/3/2018	3

<tbody>
</tbody>

Desired output:

Day/Night Shift	Date Code	Value
Day	1/1/2018	0
Day	1/1/2018	1
Night	1/1/2018	0
Night	1/1/2018	0
Night	1/1/2018	0
Day	1/2/2018	2
Night	1/2/2018	0
Night	1/2/2018	1
Day	1/3/2018	2
Night	1/3/2018	2

<tbody>
</tbody>

 

[Osvaldo Palmeiro]

Hi. Try after replacing
And Not IsEmpty(R.Cells(i, 1)) Then

by this
And R.Cells(i, 1).Value > 0 Then

Don't forget to reset ScreenUpdating to True at the code end:
Application.ScreenUpdating = True
End Sub

 

[ttray33y]

Hey all,
Ive found some code on a forum elsewhere and tried to retrofit it solve a problem im facing with formatting.

I need to insert a certain amount of blank rows based on a value. I.E: If the value is 3, i need to insert 3 blank rows below this cell. The code ive found works nicely, but when it gets to 0 it doesnt work. Can anyone cast your eye over it and let me know if you find anything that may help.

Current Code:


Input:

Day/Night Shift	Date Code	Value
Day	1/1/2018	0
Day	1/1/2018	1
Night	1/1/2018	0
Night	1/1/2018	0
Night	1/1/2018	0
Day	1/2/2018	2
Night	1/2/2018	0
Night	1/2/2018	1
Day	1/3/2018	3
Night	1/3/2018	3

<tbody>
</tbody>

Desired output:

Day/Night Shift	Date Code	Value
Day	1/1/2018	0
Day	1/1/2018	1
Night	1/1/2018	0
Night	1/1/2018	0
Night	1/1/2018	0
Day	1/2/2018	2
Night	1/2/2018	0
Night	1/2/2018	1
Day	1/3/2018	2
Night	1/3/2018	2

<tbody>
</tbody>

just replace your

IsNumeric(R.Cells(i, 1).Value)

to

R.Cells(i, 1).Value <> 0

and if you have non numeric values on the 3rd column just add

R.Cells(i, 1).Value <> 0

it should look like this:

R.Cells(i, 1).Value <> 0 And IsNumeric(R.Cells(i, 1).Value) And Not IsEmpty(R.Cells(i, 1))

 

[davie_aus]

Hi. Try after replacing
And Not IsEmpty(R.Cells(i, 1)) Then

by this
And R.Cells(i, 1).Value > 0 Then

Don't forget to reset ScreenUpdating to True at the code end:
Application.ScreenUpdating = True
End Sub

Thankyou! Works a treat

 

[davie_aus]

Thankyou! Appreciate the added input for text.