Insert Row Macro not working all of a sudden...

Hi,

I've used the following macro hundreds of times with no problem until today...


Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub


The macro looks in column B and whenever the value in the following row is different than the value in the previous row, it inserts a row.

so...

Bob
Bob
Bob
John
John
Amy
Sarah
Sarah

Becomes...

Bob
Bob
Bob

John
John

Amy

Sarah
Sarah
...


I've used it in the exact same workbook for I don't know how long...

Now, all of a sudden, whenever I run it I get a "Runtime Error 13: Type Mismatch"

I've looked and looked and tried to figure this out to no avail.

Can anyone out there take a look at this?

Thanks!

R.J.

Try this and see what happens.

Please Login or Register  to view this content.

Originally Posted by stnkynts

Try this and see what happens.

Please Login or Register  to view this content.

Hi stnkynts,

I tried your macro and I get that msg box in case of an error that says "Type Mismatch".

No idea what's going on...

Originally Posted by xladept

Hi R.J.,

Take a hard look atyour data

the data is the same as it always is. It's just a bunch of email addresses.

It splits the prefix from the domain using the @ sign.

so [email protected] becomes

Col A Col B
Row1 jsmith abc.com

and so on and so on.

Nothing out of the ordinary. Now, it does have 9000 rows, so I can't check every one of course.

Submit a redacted version of your workbook

Originally Posted by scottiex

I guess you have a formula resulting in an error in one of the cells.
For example a #N/A.

@scottiex...

Thank you, you wonderful, wonderful excel person you! lol

2 "#VALUE"'s in 9,000 rows...

deleted them and it worked fine.

@xladept: Thank you for the instructions. I was wondering how to do that...

Thanks, I appreciate the feedback