Adding/Editing Table Headers using VBA in Excel 2007 to create a Macro

Question

I'm creating a table using VBA in excel 2007. The table has headings already, but I would like it to have the built in headers from excel since they provide drop down menus where the user can select/deselect information to show. I'm wondering if there is a way to add VBA code to my macro that will allow the column headers to be deleted? (I know that adding spaces in the header will get rid of the automated name, such as column 1 or a number), but I'm wondering if there is a way to edit the code to take care of this deleting or to leave the headings blank (except for where they already exist in my table)?
Also, I have sub-headings for my table on the left side, and I was wondering if it's possible to make macro such that the subheadings can allow for selecting/deselecting information to show?
Thanks for the help! I appreciate it.

Answer 1

Before coding anything in Excel VBA try doing in User Interface.When you add table excel gives you option if you have headers or not.Default is checked so your headers are included and dropdowns are also there.

A macro is used for getting a table like below.

Sub Macro2()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$8"), , xlYes).Name = _
        "Table4"
    
End Sub

---------------------------------------------------------------------------------------------

Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

---

Asadulla Javed

Answer 2

Thank you for the reply. I have already done this, and if you were to make a larger table with headers, then you would see that each subsequent column (besides the one your named headers are in) becomes numbered or labeled. I'm wondering if it's possible to get rid of those numbers. I don't want it to say column 1 or 1 at the top of the next column. I would like my headers (that haven't been prefilled) to show and I would like the other non-headed columns to remain blank. For example, in the spreadsheet below, I would like the labels Column 1 and Column 2 to be left blank, as in column D (which I just deleted), since I have labels in certain columns, such as System Requirements in Column C.

 Also, do you know if it's possible to have left headers, so that I can have the drop down menus on the sides as well? I have included a picture below. So I would like the Exec50Requirements and the left headers below it to have the option of a drop down menu. I would appreciate it if you could let me know if this is possible, and if so, how to do it? Thanks.

 

Answer 3

As a work around you put a single space in the column headers.

And your second Q:

I am afraid it is not possible.

---

Asadulla Javed

Answer 4

Do you know if there's anyways to modify the lists so that you can get a drop-down menu list and select show all/ show none? Or is there anyways to program in VBA to make something like this work? Thanks

---

Sarah Bass

Answer 5

Sorry.I do not have any idea.

Highly hope that some expert can help you.

---

Asadulla Javed