First problem is those merged cells. You need to get rid of them if possible. Merged cells maybe are not 100% bad, but when you start using VBA code, merged cells cause extreme havoc.

Please see if you can fix the merged cells, but also, please attach a sample workbook with what you have and what you expect.

In the Dummy workbook attached, A1 in this post would be B16 in excel, and A2, B19.

Sheet1: I've un-merged the cells in A2 but the code still doesn't work, the same error pops up.
Sheet2: I would think A1 is the problem then, and tried un-merging it. Still doesn't work. Same pop up error.

Due to other cells, I would prefer if A1 remains merged. And that if you got any alternatives suggestions, it does not involve changing column width.

Attached Files

35537Dummy.xlsm (20.3 KB, 11 views)