0

I have a column containing the following values: A1 = 0 A2 = 1 A3 = 3 A4 = 4 A5 = 0 A6 = 1

I wish to select column A and insert a new column above any instance of 0.

I have tried the following code but I am getting errors and not sure what is wrong. I believe it is OFFSET that is causing the problem, I'm not 100% sure.

Dim c As Range
  For Each c In Range("A:A")
    If c.Value Like "0" Then
      c.Offset(-1, 0).EntireRow.Insert
    End If
  Next c

It says the following:

Run-time error '1004': Application-defined or object-defined error.

Then it highlights c.Offset(-1, 0).EntireRow.Insert in the code

  • 1
    How about showing us the errors you get? – sjngm Oct 14 '14 at 10:33
  • I tried posting a screen shot but I don't have enough reputation to do so. It says the following: Run-time error '1004': Application-defined or object-defined error. Then it highlights "c.Offset(-1, 0).EntireRow.Insert" in the code – NeverPhased Oct 14 '14 at 10:48
1

There is no cell at Range("A1").offset(-1), so it fails as the object does not exist. Change A1 to something else and it will work otherwise.

This is probably what you want to do -

Sub test()
Dim c As Range
  For Each c In Range("A:A")
    If c.Value Like "0" Then
      Rows(c.Row).Insert shift:=xlDown
    End If
  Next c
End Sub

But, you'll be stuck in an infinite loop with just this code. You should have something like this instead -

Sub test()
Dim d As Integer
d = Range("A:A").End(xlDown).Row
Dim c As Range
  For i = d To 1 Step -1
    If Cells(i, 1).Value Like "0" Then
      Rows(Cells(i, 1).Row).Insert shift:=xlDown
    End If
  Next
End Sub
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.