inserting cells when conditions not met, overflow error

I have a worksheet that is 4 columns by 32K 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 row.

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 my code:

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

It works for the small example (before.xls) but when I try it on my large excel file (967 KB), I get an overflow error. I'm assuming this is a memory error? Not sure.

Vanessa

This works on a small file, but there is still an overflow error with the larger file. I just split up the worksheet and ran it piece by piece and it worked. So I guess it was a memory issue that I just can't seem to shake.

Vanessa

I do not know how large your file is, but if there is no error on your code, may be change this variable.

Please Login or Register  to view this content.

In to

Please Login or Register  to view this content.

Hi -

Try;

Please Login or Register  to view this content.

Regards,
Event

Many, many thanks to all that spent time helping me with this.

After some testing, it seems that the last script by Event works the best. However, I can say that after installing more RAM (4 GB to 12 GB), it appears that all of the scripts work.

Thanks so much to everyone!

Vanessa