Exclude column when clearing content with VBA

**newrui** · 07-09-2012, 04:06 PM

Hi guys, I have a button on a sheet that clears content in any of the whole workbook's unlocked cell when clicked. How do I tell it to ignore any cell, locked or unlocked, in column B and column M?

Please Login or Register  to view this content.

**tigeravatar** · 07-09-2012, 04:31 PM

newrui,

Give this a try:

Please Login or Register  to view this content.

**newrui** · 07-09-2012, 05:18 PM

Hi tigeravatar, thanks for the quick reply, but this gives a 400 error (Excel doesn't give an explanation, just shows 400). Any ideas?

**tigeravatar** · 07-09-2012, 05:24 PM

newrui,

I am unable to duplicate that error. Can you attach an example workbook that is experiencing the issue? To attach a file, click "Go Advanced" and then click the paper clip icon to open the Manage Attachments dialog.

**newrui** · 07-09-2012, 05:44 PM

Here you go. Sorry it looks a little funny, it was a big spreadsheet, had to *** out a bunch of sensitive stuff. But it's still showing the same error for me. Excel 2010 on Win 7.

temp.xlsm

Thanks!

**tigeravatar** · 07-09-2012, 05:56 PM

newrui,

The problem is due to merged cells on the 'Input 1' worksheet. The macro recognizes the first cell, A2, as an unlocked cell, but not the rest of its merge area. Simply unmerging those cells and then running the macro again allowed the macro to complete as expected with no errors.

Generally speaking, try to avoid using merged cells at all times. There is a cell format (Center Across Selection) that does basically the same thing without merging the cells, so its highly encouraged to use that instead of merging cells.

If those cells must be merged, let me know and I'll try to come up with a workaround