Fill A Row With Zeros After Row Insert

[jsully777]

I am a beginner that needs your help. This is a really basic question:

I've inserted a row successfully. Now I want to insert zeros in the row that I just inserted (about 4 cells across).

Thanks in advance... Here is my code:

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 12) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 12) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop



Thanks,
Jim

 

[P Sitaram]

I modified your code slightly:

irow = 3
With Sheets("Heijunka Box Prep")
    Do Until .Cells(irow, 12) = ""
        If .Cells(irow, 12) > 0 Then 'Greater than 0
            .Rows(irow + 1).Insert 'Insert a whole row
            Range(.Cells(irow, 12), .Cells(irow, 15)).Formula = 0
            irow = irow + 2 'ie there is now another row to skip
        Else
            irow = irow + 1
        End If
    Loop
End With

 

[jsully777]

Not working. Its still doesn't insert a row

Not working yet.

I am trying to insert a row with this code, however, I think it's not inserting on certain rows because there is a blank empty cell (not a zero) and therefore the code just stops looking throughout the rest of the column.

So, as the code moves down thru the column and when it reaches a specific cell, I need it to do the following:
1.) If > 0, then insert one row below the number encountered
2.) Ignore all blank cells as well as it moves thru down the column.

** I want to move thru one column, inserting rows...then move thru the next column inserting more rows...etc, only when it encounters a number greater than zero (and not a blank cell).

Here is the code:

Sub InsertRows()

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 12) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 12) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 13) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 13) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 14) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 14) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 15) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 15) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop

iRow = 3
Do Until Sheets("Heijunka Box Prep").Cells(iRow, 16) = ""
If Sheets("Heijunka Box Prep").Cells(iRow, 16) > 0 Then 'Greater than 0
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
Sheets("Heijunka Box Prep").Rows(iRow + 1).Insert 'Insert a whole row
iRow = iRow + 2 'ie there is now another row to skip
Else
iRow = iRow + 1
End If
Loop


Any suggestions??

Thanks,
Jim

 

[jindon]

Hi

try the code

Sub test()
Dim i As Integer, LastR As Long, iRow As Long
Application.ScreenUpdating = False
With Sheets("Heijunka Box Prep")
    For i = 12 To 16
        LastR = .Cells(65536, i).End(xlUp).Row
        For iRow = LastR To 1 Step -1
            If .Cells(iRow, i) <> "" Then: .Rows(iRow + 1).Insert
        Next
    Next
End With
Application.ScreenUpdating = True
End Sub

rgds,
jindon