Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
47475
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
November 21, 2020
 -
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
See more

3 replies

Answer 1 / 3
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
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 2942 users have said thank you to us this month

Answer 2 / 3
Posts
47475
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
November 21, 2020
 9,075
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!