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:
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.
I encountered this problem today and was flummoxed. I did a Google search on ” vba table column name symbol” and your post was the top hit. The single tic in front of the hashtag worked perfectly. Thanks so much for saving me tons of time on this ridiculous special case.
