Help finalizing some VBA code to create two new rows above current row instead of one new row above and one new row below

ubergreen

New Member
Joined
Jun 13, 2024
Messages
33
Office Version
  1. 2021
I found this code online and have been trying to modify it to fit what I need it to do. The only thing is with this current code is that it creates one new row below and one new above the row that this code applies to. I want both new rows to be created above the row.

VBA Code:
Sub new_rows()

For i = 2 To 1000

    size1 = Cells(i, 2).Value
    size2 = Cells(i, 3).Value

    
    If size 1 <> "" And size 1 <> 0 Then
    Cells(i, 1).Offset(0, 0).EntireRow.Insert
    Cells(i, 2) = size1
    i = i + 2
    End If



    If size 2 <> "" And size 2 <> 0 Then
    Cells(i, 1).Offset(0, 0).EntireRow.Insert
    Cells(i, 2) = size2
    i = i + 2
    End If


Next

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
See if this works as you expected.
Code:
Sub test()
    Dim i&, x&, a
    For i = Cells.SpecialCells(11).Row To 2 Step -1
        x = Application.Count(Rows(i).Range("b1:c1"))
        If x Then
            a = Rows(i).Range("b1:c1").Value
            Rows(i).Resize(x).EntireRow.Insert
            If x = 1 Then
                Rows(i).Range("b1").Value = Application.Sum(a)
            Else
                Rows(i).Range("b1:b2").Value = Application.Transpose(a)
            End If
        End If
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top