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

Solved/Closed
Kwanni Posts 2 Registration date Monday June 24, 2013 Status Member Last seen June 26, 2013 - Jun 24, 2013 at 01:06 PM
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - Jun 26, 2013 at 04:54 PM
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
Related:

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 26, 2013 at 02:28 AM
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
2
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,164
Jun 26, 2013 at 04:54 PM
Venkat is our home genius!
1
Kwanni Posts 2 Registration date Monday June 24, 2013 Status Member Last seen June 26, 2013
Jun 26, 2013 at 12:57 PM
Thank you so much for taking the time to help me.
Saved me hours of work.
0