Getting compile error 1004

Hi friends,

I want to merge cells if the column ‘B, d, f, h, j, l, n, p, r, w’ are blank.
If column ‘B’ is blank then merge columns ‘A:B’.
If column ‘d’ is blank then merge columns c:d’.
If column ‘f’ is blank then merge columns ‘e:f’.
If column ‘h’ is blank then merge columns ‘g:h’.
If column ‘j’ is blank then merge columns ‘i:j’.
If column ‘l’ is blank then merge columns ‘k:l’.
If column ‘n’ is blank then merge columns ‘m:n’.
If column ‘p’ is blank then merge columns ‘o:p’.
If column ‘r’ is blank then merge columns ‘q:r’.
If column ‘w’ is blank then merge columns ‘v:w’.

I want to repeat it for each subject ‘s1:s9 and t’.
The range of marks is row 26, 44, 62, 80, 98 and so on skipping 17 rows endlessly as per the students in the class.

I’m getting compile error 1004.

Any help will be highly appreciated.

Thanking you in anticipation.

Hello mso3,

You can not check if an entire entire column is empty or merge cells in two columns row by row in VBA using this syntax.

Please Login or Register  to view this content.

This macro has been added to the attached workbook. It will merge the cells in the columns as you asked.

Please Login or Register  to view this content.

Hi Leith Ross,
Excellent! Excellent! Excellent!

What a great solution!

It's working absolutely fine beyond my imagination.

I by heart appreciate you for the challenging solution.

Thank you and have a nice day.

Hello mso3,

You're welcome. Glad I could help out.

Hi Leith Ross,

The code is working fine but I want to merge the cells in center. I used

Please Login or Register  to view this content.

But it’s not working. I’m getting compile error ‘variable not define’ on the word ‘center’.

Therefore, I request you to do amend the code to merge and center the cells positively.

Thank you and sorry for trouble.

Hello mso3,

Here is the updated macro. The change s are in blue.

Please Login or Register  to view this content.

Hi Leith Ross,
Perfect! Now it's working fine.

Thank you and have a nice time.