# Code that inserts rows based on cell value

#### hgufrin

##### Board Regular
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)) = ""

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

thanks Steve,

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

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

Code:
``````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)

njimack,

not workin for me. must me too much information

thanks anyway

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.

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!

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.

Replies
0
Views
245
Replies
0
Views
231
Replies
6
Views
215
Replies
4
Views
538
Replies
1
Views
670

1,219,798
Messages
6,150,319
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back