Populating Hierarchical Data via VBA with named table.

Hai. Long time lurker, first time poster.

I have a report coming of hierarchical data, coming out of SAP, that I need to use to extrapolate metrics, etc. This data isn't very useful as a pivot table as each level requires data manipulation in order to populate all of the required fields so that Excel knows how to organize it.

I've created a very rudamentary script that auto-populates the data on command but it runs extremely slow. This data amounts to over 5000 rows and will continue to grow. Yesterday it took over two hours to complete so there must be a quicker way to accomplish what I'm asking.

In a nutshell, the data looks like this:
Capture_zpse2617b69.jpg

and the code looks like this:

Please Login or Register  to view this content.

Also, for your amusement, I have attached a dummy worksheet with 1200 lines of data for testing (and the code).

The work has to exist in VBA as the data will be replaced (copy-paste) on regular intervals, so anything in the cells will be wiped out each time (SAP creates a new .xls file after running the report) and I run this on-demand as opposed to an event so not as to slow down the other tabs within the worksheet.

The logic behind the code is simple. If the lowest level doesn't have any data, the next lowest won't repeat (pivot table assignment), and so on.

Example:
Capture_zps6894f26c.jpg

Unfortunately, I can't think of a better method to do this. I thought about using the autofill, but wasn't sure the best way to identify where "level 6" started and where "level 5" should stop. Really, I was completely thrown off on how long my code took to actually complete.

Thanks.

If your question is, can I move the data to another worksheet, I think that would defeat the purpose of my goal. The data needs to populate in place, although I'm open to hearing alternatives.

This code took about 0.75 seconds on my computer with that sample.

Please Login or Register  to view this content.

Yraen, thanks for the code.

So I implemented your suggestion and it started to take the same amount of time, so I started to try to figure out what the variables were between my sample and my actual sheet. There are calculations on the same sheet, as well as Pivot Tables on other sheets.

So in the end, what I REALLY needed was this:

Please Login or Register  to view this content.

Once I stopped calculations within the workbook, the code picked right up and only took a few seconds on my original code. So while I ended up not using your code, you helped me identify a different issue. So thanks for your help!

You're welcome. I tend to do everything with VBA as it just seems faster to me. You might look and see if you can remove all of the formulas you have in all of your cells and replace it with something in VBA.