Code that inserts rows based on cell value

hgufrin

Board Regular
Joined
Apr 19, 2004
Messages
172
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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

Board Regular
Joined
Apr 19, 2004
Messages
172
thanks Steve,

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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)
 

hgufrin

Board Regular
Joined
Apr 19, 2004
Messages
172

ADVERTISEMENT

njimack,

not workin for me. must me too much information

thanks anyway
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

Board Regular
Joined
Apr 19, 2004
Messages
172
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,463
Messages
5,658,928
Members
418,476
Latest member
Tristram_ZX81

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top