insert multiple cells in row if contents of one cell do not match contents of another

I have a worksheet that is 4 columns by 16331 rows. I want to shift the contents of cells A through C in row n down if the contents of cell C for that row n do not match the contents of cell D for that row n. The contents of column D should never shift. I constructed a for/then/next loop so that the macro will ask whether the contents of cell C row n match the contents of cell D row n for each n until n=16331.

I have pasted an small example below to illustrate an example of what I have (before) and what I would like to accomplish (after).

Here is the macro:

SSub Move_totals_down()
Application.ScreenUpdating = False
Dim r As Long
Dim n As Long
r = Range("C16331").End(xlUp).Row
n = Range("D16331").End(xlUp).Row
For i = 2 To r
For l = 2 To n
If Range("C" & i).Text <> Range("D" & l).Text Then
Cells(r, "A:C").Insert Shift:=xlDown
End If
Next l
Next i
Application.ScreenUpdating = True
End Sub

Nothing happens when I run it. I guess it's better than an error message, but not by much.

Vanessa

Hi Vanessa. Try:

Please Login or Register  to view this content.

Thanks for your reply!

Ok, so we're close, I hope...

The above macro copied all of the cells where the values for cells in column C do not equal D, which is basically the first three rows worth. What I think I need to have is something of the variety

Sub insertcells2()
Dim bottomC As Integer
bottomC = Range("C" & Rows.Count).End(xlUp).Row
Dim c As Range
For Each c In Range("C1:C" & bottomC)
If c <> c.Offset(0, 1) Then
Range("A" & c.Row, "C" & c.Row).Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next c
End Sub

So that I can set up a for loop where if C does not match D, 3 cells are entered in columns A through C and then the whole thing runs again.

Vanessa

Hi Vanessa. I'm not sure what you mean by:

3 cells are entered in columns A through C and then the whole thing runs again

Do you mean that you want 3 blank rows (not cells) entered below the row where C doesn't match D? Could you please clarify a little further?

I just want to shift the content in columns A through C down, leave column D alone, and re-evaluate again.

I tried the following:

Sub TestNewUSE()
Dim bottomC As Integer
bottomC = Range("C" & Rows.Count).End(xlUp).Row
Dim c As Range
For Each c In Range("C1:C" & bottomC)
If c <> c.Offset(0, 1) Then
Range("A" & c.Row, "C" & c.Row).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next c
End Sub


And it worked for my example "before" file, but now I get an overflow error when I try to run it on my large (16K row) file.

Vanessa

I'm sorry Vanessa. I have no idea why you are getting the error. Perhaps it's due to the large size of the file. Maybe someone else out there with more experience than I would have an answer. You could start a new thread explaining the error problem. Again, my apologies.

No need to apologize! I very much appreciate your help!

Vanessa