Runtime 1004 Error - Cannot Use that Command On Overlapping Sections

Hi,

If you have multiple discontiguous rows in the same column(s) with comments, that will create an overlapping range, which is why you can't perform the insert. You should be able to correct that using Intersect

Please Login or Register  to view this content.

You could just use 1 line.

Please Login or Register  to view this content.

I am going to assume that you have merged cells and that is why you are getting the error

Originally Posted by xlnitwit

Hi,

If you have multiple rows in the same column(s) with comments, that will create an overlapping range, which is why you can't perform the insert. You should be able to correct that using Intersect

Please Login or Register  to view this content.

Ah, that makes sense, thanks.

I have also misinterpreted the use of shift:=xlToRight.

What I would like to do is insert a new column to the right of all columns which contain comments - what's the easiest way to do that?

Thanks in advance

I think this should achieve that

Please Login or Register  to view this content.

Amended code- I had forgotten that the insert would create an endless loop using a For Each loop

Please Login or Register  to view this content.

I just ran my last code against that sample, and the result is exactly the same as your After sheet.

The CommentsColumns range is made up of several distinct areas (adjacent columns count as one area). The For Each loop loops through each area, and then the For loop loops backward from the rightmost column of each area and inserts a new column to the right of each column. It has to loop backwards to avoid the original issue where inserting a column increased the size of the area and caused an infinite loop.

Many thanks.

Apologies again, but a couple of other questions for you.

I am stepping through this using F8 and whilst I can understand what the code is doing, I can't for the life of me work out the logic of the order of the columns/areas it looks at to add columns? For example, in the s/s I uploaded, the code adds a column after column S? Why does it start there as opposed to adding a column after column C or even W? If I keep stepping through, there are other illogical jumps over columns with comments as well. It eventually captures all columns with comments but I'm confused by the logic.

And what is the purpose of shift:=xlToRight?

The reason I ask is that if I run the code through without it, the end result is the same?

The first cells with comments in, reading left to right and top to bottom, are in row 3 columns R and S. This is therefore the first area obtained by specialcells.

The Shift parameter doesn't make a difference when working with entire columns but does if you are only inserting- or deleting- parts of a row or column.

My pleasure- it's always nice to come across someone who wants to understand the code rather than blindly implementing it!

Thank you for the rep too.