Creating Tables the Right Way

And by ‘right way’ I mean the way I want. JKP commented:

I wish MSFT would put a tablename box right into the “Format as Table” dialog as it is the first thing I do after formatting a range as a table. O, and always put that checkbox on. My tables always have a header row.

I couldn’t agree more. So why not repurpose Ctrl+T to do what I want.

This code makes a lot of assumptions about how I work with tables, so it may not work for you. First, I ask for the table name. I start all my table names with tbl, so if I don’t include that the code includes it for me. Next, I create a new ListObject based on the CurrentRegion of the ActiveCell. This is different than what Excel does. If you only have one cell selected, Excel will use the CurrentRegion. If you have more than one cell selected, Excel assumes you’ve defined the range you want and uses that. I put one table on one sheet and it’s the only thing on there. Therefore, I always want everything on that sheet to be the table.

Next, I see if a table with that name already exists on the sheet. If it does, skip the whole naming part.

Finally, I change the name of the sheet if it’s still named the generic ‘Sheetx’. I drop the ‘tbl’ part from the DisplayName property and name the sheet. The error avoidance is in case there’s already a sheet with that name. In that case, the name remains unchanged.

Why the DisplayName? If you name a table

, you can’t name another table

on the same sheet. In fact, in the user interface you can’t name another table

in the whole workbook. But in code, you can name another table

as long as it’s on a different sheet. If that name already exists, ListObject.Name remains

, but ListObject.DisplayName is changed to

. That’s why I check for the existence of that table on the same sheet but not the whole workbook. And that’s why I use the DisplayName to name the sheet.

I should have skipped all this error checking and just put a big

at the top. I probably will never have two tables with the same name, and if I accidentally did, it would just keep the default name.

Posted in VBA

29 thoughts on “Creating Tables the Right Way

  1. One thing I hate about Tables is that Excel isn’t programmed smartly enough to handle what happens if you have some unrelated text sitting above your nicely formed table. Will post a screenshot in a second. I was going to change your code to handle this situation, but I don’t have time at the present moment.

    Here’s that screenshot:
    Bad Table Range

  2. I just ran into a situation where I didn’t want CurrentRegion. I imported a text file, cleaned up the data, and prepared to make a table. But the CurrentRegion extends out where old data once existed, but doesn’t any more. So I guess I need to account for selecting the table.

  3. Dick:

    fails for non-English Excel versions as sName will contain a translated string (Onwaar” in Dutch). Better is to change sName to a variant and then test type:

  4. That’s where (VBA.)inputbox, instead of appclication.inputbox comes in:
    Click on ‘Cancel’ in both cases.

  5. Great approach. Is someone willing to manipulate the code a bit so it would add also a name range for the table? I use it a lot for dashboarding.

  6. I used InputBox originally and then switched to Application.InputBox because InputBox doesn’t return False. But I don’t see any downside to checking Len(sName), so I should have stuck with InputBox.

  7. Dick: I use range names to use in Data Validation and formulas, so when I create a table and name it I also select the range of data and name it.

  8. Mike – Tables are essentially a collection of named ranges that Excel maintains automatically on your behalf, and so you can already refer to them in your formulas. Granted, the Data Validation dropdown doesn’t accept Table Names, but if you just point your DV list at the cell range where say a table column is, then your list will automatically update if that list grows or shrinks. So I’m not sure why you need to assign names to Tables. Can you elaborate?

    About the only time I add Range Names to Tables is when I want to manipulate them via VBA, so that if the user (or me) changes the name of a table element (e.g. changes the column header) then the code will still work.

    Here’s some content from a book I’m writing that illustrates the Dynamic Named Range aspect of Tables. Note that in the below screenshots I’m NOT using Table Names in my formula but just plain cell references…I introduce the concept of Table Names the next page over:

    Here’s a table that has some conditional formatting on the Total column, as well as some other things that point at it: a formula, a data validation dropdown, and a chart.
    Table 1

    At the moment, the table above has got our weekly food budget in it: Vege, Fruit, and Meat. Hardly a balanced diet. Watch what happens when we add a fourth row to that table, to hold an important staple we left out:
    Table 2

    Wow, will you take a look at that: the Table expanded like a stomach to automatically hold our new category of ‘Beer’. But even better, that conditional formatting got copied down to the new row, and the formula, chart, and dropdown that reference our table all got updated automatically, before we could say ‘Prost’. Spooky! So use a table, and you never need repoint formulas, conditional formatting, charts, and dropdowns again!

  9. You must be living in a thorny thirsty country….

    Can’t decipher whether it’s a daily, weekly, monthly or elsely budget.

  10. For the very simple case of importing text data and then formatting that as a table I have a simple keyboard shortcut hack. Assuming the ActiveCell is the Top-Left cell of the table, do a Ctrl-B before the Ctrl-T. It seems the Insert-Table dialog will think the first row is a header if it detects some bold formatting there, even in just one cell.

  11. Hi again, Dick

    Although is true Data Validation can be used just by pointing the reference where the table is, I’ve noticed it’s a bit annoying to do so every time I want to use a DV list, that’s why a name is useful, then you only have to write it in the formula dialog.

  12. Mike: good point. Especially given that you can push F3 to bring up the Paste Name dialog box, meaning you don’t even have to type that name in there, let alone point to a cell reference.

    F3

  13. This is the code to add a name:

    I guess this must not be very difficult to implement in the existing code, I’ll try it.

  14. Ok, I think I’ve got it. The code I pasted was wrong, but I was able to get this to add a name just for the data range of a table:

    So, adding this little bit of code after the last End if of Dick’s code, seems to work:

  15. @snb

    True. Sorry, does it do the same? I’m still learning the basics and some coding it’s still obscure for me.

Leave a Reply

Your email address will not be published. Required fields are marked *