Insert a row with specific contents after every merged data cell.....

[wolfwood]

Hi all

I've looked online for VBA code to insert a row with specific contents (ex. 'Example1' after every merged data cell (it should check for data) in a specified range (B13:B269), but none seem to work properly with the merged cells. Column B has merged cells, but column D does not (Column C is blank, and non-merged).

So, it looks something like this:

Column B Column D
MergedCell1 Data1a
MergedCell1 Data1b
MergedCell2 Data2a
MergedCell2 Data2b
MergedCell3 Data3a
MergedCell3 Data3b

And after the code, I'd like it to look like this:

Column B Column D
MergedCell1 Data1a
MergedCell1 Data1b
Example1
MergedCell2 Data2a
MergedCell2 Data2b
Example2
MergedCell3 Data3a
MergedCell3 Data3b
Example3

If possible, I'd like this to apply to the whole workbook as every sheet is identical in terms of the number of rows.

Many thanks!

~J

 

[MickG]

Perhaps this:-

.MergeArea(1).EntireRow.Insert
                .MergeArea(1).Offset(-1).Value = "example" & c
                .MergeArea(1).Offset(-1).Interior.ColorIndex = xlNone ' Add this line
            End If

 

[wolfwood]

MickG, with a few adjustments, that's perfect. Thanks for your patience and time.

~ J

 

[MickG]

Great , Pleased you got it working
Regrds Mick