This is a short one, but a REALLY specific one.

Recently (i.e., today) I was tinkering with some List Objects, a.k.a. “tables” in VBA. All I was trying to do was to clear the contents of a column. Easy enough. It looked something like this:

Sheet1.Range("Table1[# Resource Req'd]").ClearContents

No Excel love. It kept throwing me an error. Specifically, error 1004, “Method ‘Range of object ‘_worksheet’ failed”.

What could I be doing wrong?

I started with the two best posts on Excel tables. First the Spreadsheet Guru’s post giving an introduction to tables. I couldn’t quite find what I was looking for, so I jumped over to Jon Peltier’s post on tables. It’s a little more detail than the overview by the Spreadsheet Guru. Unfortunately I couldn’t find what I needed there either, but it did give me an idea as to the problem.

You see, I have two “special” characters in the column header. Specifically, the hastag (#) and the apostrophe (‘). I figured my error had something to do with these, but neither post, nor a cursory Google search turned anything up when using them in VBA.

So I went the old school route. I recorded a macro wherein I highlighted the column in the table and then used Right-Click > Clear Contents.

What I found was that the code added an extra apostrophe before each special character. I’ve never seen this or needed to use it in any other code I’ve written for Excel, so maybe it’s specific to List Objects only. Then again, maybe not. Truth be told, I wasn’t interested in the history behind the extra apostrophe, I just needed the damn thing to work.

So now the code looks like this:

Sheet1.Range("Table1['# Resource Req''d]").ClearContents

And it works beautifully.