Code that inserts rows based on cell value

[hgufrin]

Hello all,

Would someone tell me why the code below is not working? I am trying to insert a row in between 16034 & 16031.
Objective: every time there is a number change (in column A) I would like to insert a row. The numerical data is tens of thousands of lines long.
Thanks for your time. I appreciate it!

Data Set
16034
16034
16034
16034
16031
16031
16031

x = 2
Do
ActiveSheet.Cells(x, 1).Select
If ActiveSheet.Cells(x, 1) <> Selection.Offset(-1, 0) Then
Selection.EntireRow.Insert
Else
End If
x = x + 2
Loop Until Trim(ActiveSheet.Cells(x, 1)) = ""

 

[SteveO59L]

Heres some code I use to do the same thing

Dim c As Range, rng

Set rng = Range("b10:b" & Range("b65536").End(xlUp).Row)

For Each c In rng
If c.Value <> "" And c.Offset(1, 0).Value <> 0 Then
If c.Value = c.Offset(1, 0).Value Then
Else
'For i = 1 To 5 Step 1
c.Offset(1, 0).EntireRow.Insert shift:=xlDown
'Next i
End If
End If
Next c

 

[hgufrin]

thanks Steve,

this appears to be easier. just can't figure out what is wrong with my code. have good weekend

 

[njimack]

I think it's because of the x=x+2. This should be INSIDE the IF statement...

Sub test()
Dim x As Integer
x = 2
Do

If ActiveSheet.Cells(x, 1) <> ActiveSheet.Cells(x, 1).Offset(-1, 0) Then
Cells(x, 1).EntireRow.Insert
x = x + 2
Else
x = x + 1
End If

Loop Until Trim(ActiveSheet.Cells(x, 1)) = ""
End Sub

(note that you don't need to select cells to evaluate them)

 

[hgufrin]

njimack,

not workin for me. must me too much information

thanks anyway

 

[njimack]

not workin for me

What happened when you tried my code? Did it error out? Did ANYTHING happen? Have you tried stepping through it line by line?

I tested it on the sample of data you provided and it worked fine.

 

[hgufrin]

njimack,

ok... i took out the first line

Dim X as Integer... and it worked fine.

With all of the other code that I have in the macro it declared duplicate declaration in scope....

Then i took out the first line and it works.

Do you think it is necessary? Thanks!

 

[njimack]

It obviously wasn't necessary to you as you had already declared the variable X. I included it in my code so that I could test the code was working.

Glad it's working now.