Need a way to quickly format reports; ran into a problem with slow VBA, need a workaround.

[Lurkily]

So, we have reports that regularly need to format quickly for meetings. Doing it by hand under time pressure introduced error. I'm trying to set up excel sheets to make that happen. The key is that I need to be able to give these to other people so they can run them locally, and they can push a button and it'll work.

I used to have a sheet with a query that was tricked into accepting a dynamic reference; this meant I could say "Put these two in the same directory and push the button." But we updated to Excel 365, which sometimes pulls from onedrive, sometimes pulls from the local drive, and trying to accept either seems very difficult to write as a query. Especially in a way I can give to someone else.

Right now, what I'm looking at is VBA scripting, so they can open the reports and the formatter, push a button, and it's formatted. If works really well for one sheet. But this other one, I need to remove some rows from the sheet. Several thousand. And the usual solution seems to be looping through each row and doing individual deletions which is WAY too slow.

Am I on the entirely wrong methodology, should I be using a different tool? Or is there something stupid I'm overlooking?

Please forgive me if this isn't entirely coherent, I've already melted my brain a bit, and I've just learned dug into VBA this last week by bouncing questions and modification needs off ChatGPT.

 

[jkpieterse]

You could filter the table for rows you want to be ignored and then delete all of them in one go. That should be fairly quick.

 

[SeniorNewbie]

If you want to use (aside the proposal above) VBA anyway you can accelerate the process by using arrays, dictionaries or collections. Then all the data is in the RAM only. Formatting the data in a sheet is very the last step.

 

[Lurkily]

Okay, maybe I should clarify; I am entering VBA at a level of capability of "I google pre-existing solutions a lot, and know how to ask ChatGPT intelligent questions and get a reasonably effective answer." I'm not very familiar with using arrays, dictionaries or collections. (I've used arrays to a limited extent, but still don't fully understand them.)

Right now I'm trying to pursue a methodology of "sort alphabetically, delete everything after the last instance of 'XYZ,' sort reverse alphabetically, repeat."

If there's a cleaner method for approaching that, I'd like to hear more about it. I need to move on to other things, soon, but I will probably try and make this cleaner in the future. Just knowing what questions and methods to pursue, Google, and ask ChatGPT about would be super useful.

I know people hate ChatGPT . . . but holy moly, it's good for learning how to do things you don't know how to do. Just as long as you can test it RIGHT away, so if it feeds you bull, you can call it on it without embedding wrong ideas.

 

[SeniorNewbie]

I'm a peaceful guy hating definitely nothing. But what I learned in my long lasting life the best way is: live to learn.

What you want to do in XL is not very difficult and your idea "Right now ..." points to a simple solution. Instead of Google & Chat check some tutorials about VBA and you'll learn. Any other way is only copy 'n paste without understanding what happens... sorry

 

[jolivanes]

I am sure if you explain in a clear concise manner without leaving anything out, you'll get a suggestion in this forum that Chat e.o like it can only dream about.

 

[Lurkily]

I actually got the answer from our local tech wizard, which is pretty useful in figuring out how to move forward.

I am sure if you explain in a clear concise manner without leaving anything out, you'll get a suggestion in this forum that Chat e.o like it can only dream about.

I mean, at least it would be an answer. This irritated me enough that I put the text of my question here in the forum into ChatGPT exactly as written, just to see if it would be useful.

Prompt: I'm not very familiar with using arrays, dictionaries or collections. (I've used arrays to a limited extent, but still don't fully understand them.)Right now I'm trying to pursue a methodology of "sort alphabetically, delete everything after the last instance of 'XYZ,' sort reverse alphabetically, repeat."If there's a cleaner method for approaching that, I'd like to hear more about it.

Relevant part of the really wordy response:

1. Load Data into an Array: This allows you to work with the data entirely in memory, which is faster.
2. Filter the Data in the Array: Retain only rows that match your criteria.
3. Write the Filtered Data Back to the Worksheet: Overwrite the original data with the filtered result.

So, yeah, maybe this community's capable of giving me an answer, but at least it provides more answer than snark.

 

[jkpieterse]

If you can give us a clear understanding of the structure of your table containing the data (name of sheet, name of the column to be filtered and its relative position in the table, value you are filtering for, if this is a filter to delete the rows or to keep them, if the table is formatted as table -which you should do at all times anyway-, etcetera), we can help writing your code. Also, you seem to have working code already, post it here and tell us what needs improving.

 

[Lurkily]

There are a variety of reasons I can't; mostly, I'm working on a secure environment, and would have had to construct dummy data for a sample data set, and the script I was using had things like file paths within our network in it, and I am not entirely certain what IT considers safe to share.

In the end, I opted to describe my approach and ask what the correct approach would be. I did eventually come to an answer, and I do have working examples for most of the sheets I'm updating.

I'm still working on one where I have to merge data from two sheets into a single output and add some conditional formatting and formulas in the process, but I'm making steady progress. One little sub-task at a time.

 

[SeniorNewbie]

like file paths within our network

That's already an item you can optimize: Never use absolute paths, but relative ones. So you can move the workbook from PC to PC just ba copying the folders to somewhere else e.g.

sPathOutput = ThisWorkbook.path & "\" & Output

. That should be easy to do for an example dummy.