Clearing Contents Below Merged Cells

I have some code that worked perfectly until I added a logo with merged cells above the data. Now I get a message about not working with merged cells. I changed the reference to point to the cells below the merged cells but it still does not work.

Please Login or Register  to view this content.

The bold line above -- .Parent.Columns(1).ClearContents -- seems to be the problem; it sees the merged cells above and fails.

I want to clearContents but only from row 8 and down in column A.

Your suggestions would be most appreciated.

Hi,

Could you please upload your workbook so that I may debug the code in context.

Thanks

I have attached a sample; the actual workbook is huge. Please let me know if example is not sufficient. Thanks.

Sorry to be unclear, but I will need to see the code in action, so I will need a desensitized copy of your actual workbook so that I can run the macro and debug it.

Let me know if you have any more questions

All i want to do is change that one line of code so that it clears contents below the merged cells. If another file is essential for this let me know.

Sorry if I am being a pain here, but I will need the actual workbook. I just tried testing the line with the error and it goes off without a problem. And I can't implement your entire code as I don't know what values are on the "Assignments" worksheet.

Unfortunately, this makes it impossible to debug your code without the actual workbook

Hi ajryan88,

Thanks for the attempt. It had the same error "We can't do that to a merged cell." Guess I will need to cobble together a better sample.

tom

Thanks,

I just need a sample that includes the "Assignments" worksheet (at least part of it), as well as some merged cells on the main worksheet in the same location as in the actual worksheet, and it must include your code in the worksheet module.

Thanks again

Actually, another random thought, but this may just overcome the merged cell problem. Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I think this one has a better chance of working than my last proposed code change.

*Fingers crossed*

Thanks.

Try this new code and tell me if it still functions correctly. The code doesn't fail, but I may not have replicated what the old code did, so please just let me know and I will be happy to make any changes that are necessary

It works! But it takes an awfully long time. Is it possible to somehow determine how many rows are populated from prior run and then clear only those cells?

Hi,

It already does predetermine how many rows are populated. What is taking a long time are the calculations in the other columns of the spreadsheet. When I run the code, it populates really fast, but then Excel takes ages to calculate the spreadsheet (you can see this in the bottom-right hand corner of the status bar). To test this theory, try putting Excel into manual calculation mode (Calculation Options under the Formulas tab), and then run the macro, and then press F9 to calculate the worksheet manually. With any luck, everything goes like lightning until you press F9.

Hope this helps

Thanks, ajryan88! The code works perfectly! Thanks again for the help. Have a super day. tom

No problems!

Please don't forget to click on the * next to my post to say thank you

You have a great day too

Comments are disabled for non logged in users so I'm posting this to help people

Answer was:

.Parent.Columns(.MergeArea.Columns.Count).ClearContents

If you need to just clear contents of merged cells and dont mind unmerging them

clear_range.UnMerge
clear_range.Clear

should do it