Run Time Error in coding - Can someone help me debug please?

I have attached an example file with a macro that strips data found in one column from matching data in another column.

I get a run time error on the line:
arrVariant = rngVariant.Cells(1, 1).Offset(1, 0).Resize(lngLastRow - 1, 1).Value

Could someone help me fix the script please?

Please Login or Register  to view this content.

I'm pretty sure its the blanks in column E causing it. Sometimes, like column D, column E will have data and sometimes not. Any ideas?

The variable lngLastRow contains a value of 1, so you're trying to resize the range to have zero rows.

I'm not quite sure what you're trying to do, so I can't really tell you how to fix it, but if want to give me some more detail I'll have a go.

So there are two columns that contain variant information, size and colour, columns D and E. The product model name, column G, also sometimes contains this data but I need to strip these words out where it does find a match in the variant columns. If you open the example attached you will see what I mean. It works if I populate row 2 & 3 in column E with some text.

Thanks very much!

OK, I think I understand.

This is how I'd tackle that:

Please Login or Register  to view this content.

Does that help you out?

That almost works a treat.

Where there is no size or colour it removes the spaces from the product model name. Any way to modify it for that?

Try changing this line:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

That works great. Thank you very much!