Trying to write a code to insert a blank row above a cell valueSolved/Closed

Question

I am trying to write a code that will auto insert a blank row above a certain cell value ie:

existing:

Column G

5
10
20
22
999
2
22
55
999
22
32

Need it to look like this:  

Column G

5
10
20
22

999
2
22
55

999
22
32

Everytime the value in a cell in column G is 999, a blank row is inserted above it. 
Thank you

venkat1926 · Accepted Answer

when you entere numbers without unnecessary space before or after the number 

then run this macro 

Sub test()
Dim r As Range, cfind As Range, add As String
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
Worksheets("sheet1").Activate
Dim j As Integer, k As Integer
j = Range("A1").End(xlDown).Row
For k = j To 1 Step -1
If Cells(k, 1) = 999 Then Cells(k, 1).EntireRow.Insert
Next k
End Sub

Kwanni · Answer

Thank you so much for taking the time to help me.
Saved me hours of work.

Ambucias · Answer

Venkat is our home genius!

Trying to write a code to insert a blank row above a cell value

3 replies

Subscribe To Our Newsletter!