Previous Topic Next Topic

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

Solved/Closed
Report
Posts
2
Registration date
Monday June 24, 2013
Status
Member
Last seen
June 26, 2013
 -
Posts
47367
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
 -
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

Answer 1 / 3
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Answer 2 / 3
Posts
47367
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
 11,264
Venkat is our home genius!
Answer 3 / 3
Posts
2
Registration date
Monday June 24, 2013
Status
Member
Last seen
June 26, 2013
Thank you so much for taking the time to help me.
Saved me hours of work.
Ask a question