LinkBack URL
About LinkBacks
I'm trying to create a table of data in an excel file. The file already exists with headings from A-N but the number of rows could be anything. How can I create a table in VBA that will make a table out of data with no set range?
I'm trying to create a table of data in an excel file. The file already exists with headings from A-N but the number of rows could be anything. How can I create a table in VBA that will make a table out of data with no set range?
Hello mtma,
Your question is very broad. An example would help. If you just want a general answer, here is a way to grab all data from a sheet into an array:
Please Login or Register to view this content.
1. Include a sample workbook with an example of the result you want
2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.
I'm not sure how better to explain it. I have an array of data (n x 18) in an excel sheet and I want to make a macro that will create a table from that data.
What do you mean by "table"? Isn't it already a table? Do you want a pivot table? Do you want an array?
It is currently just data in a cell but I want it to be formatted as a table
Sorry, you're going to have to provide a sample for me to understand. To me, data in cells and table are the same thing, unless you're trying to do a text-to-columns split. I can't tell if you want to summarize the data, parse it, store it in memory, or just format it. An example workbook would make things more clear.
Hi mtma. Did you ever find a solution to your question? I think I have the self-same requirement, ie find a VBA means of doing the same as selecting a single cell in a block of data, and using Insert/Table. At this point Excel identifies all the contiguous data and, if you OK, it creates the new table based on that range. With the VBA solution, I need to be able to run the code on a block of data that could be any number of rows deep and have it automatically set the correct range from which to create the table object. Hope I've described it clearly!
P
... and I just answered my own question:
Sub ctable()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Tbl001"
End Sub
This assumes that the range includes cell A1; if not, substitute any cell within the block and CurrentRegion will pick up the correct range. Hope this helps.
P
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks