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
47363
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
- 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

3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
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
47363
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,280
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