VBA to copy and insert row below current row if a value is greater than zero

Scottyp123

New Member
Joined
Dec 16, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I have a spreadsheet where rows 12 through 255 I want to copy if a value in column G in that row is > 0 row and insert below the current row. What I would like to do is make it so that once the value in G is set back to 0 or nothing"" the new copied row is removed. Additionally I want to set it up so that it will copy a row only 4 times, after the row is copied 4 times it would no longer make a copy. Not sure if this is possible
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
Hi Scottyp. It seems like a bit of a challenge but if I understand your needs correctly this seems to work. U need to have an empty column for it to work. I used "H" for this test. I also used "Sheet1" so U will need to adjust these 2 things to suit your needs. HTH. Dave
Code:
Sub Test()
Dim Cnt As Integer
For Cnt = 12 To 255
If Sheets("sheet1").Range("H" & Cnt) = 0 Or _
    Right(Sheets("sheet1").Range("H" & Cnt), 1) < 8 Then
If Sheets("sheet1").Range("G" & Cnt) > 0 Then
If Sheets("sheet1").Range("H" & Cnt) = 0 Or _
IsNumeric(Left(Sheets("sheet1").Range("H" & Cnt), 1)) Then
Sheets("sheet1").Range("G" & Cnt) = 0
Sheets("sheet1").Rows(Cnt).Copy
Sheets("sheet1").Rows(Cnt + 1).Insert
Sheets("sheet1").Range("G" & Cnt + 1) = vbNullString
Sheets("sheet1").Range("H" & Cnt + 1) = vbNullString
Sheets("sheet1").Range("H" & Cnt) = _
                    "T" & Sheets("sheet1").Range("H" & Cnt) + 1
Application.CutCopyMode = False
Exit For
Else
Sheets("sheet1").Rows(Cnt + 1).Delete
Sheets("sheet1").Range("G" & Cnt) = 0
Sheets("sheet1").Range("H" & Cnt) = _
                  Right(Sheets("sheet1").Range("H" & Cnt), 1) + 1
Exit For
End If
End If
End If
Next Cnt
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,749
Messages
5,626,636
Members
416,197
Latest member
Kartik201997

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
Top