Why is this code so slow?
Hi all,
this code is running insanely slow (measured in minutes rather than seconds). Bearing in mind that the number of 'cells' is going to be no more than 200, why should this be the case? I can't think of anyway to speed this up. Can't really share the full file as it is chock full of personal details, but any questions please ask.
This is the effect I am after:
Please Login or Register to view this content.
tableexample.jpg
You haven't provided us with how you calculate 'RCnt', this may be the issue.
whoops, sorry:
rCnt = Sheets("Personal").Range("B6500").End(xlUp).Row
Can you provide a before and after screenshot of what it is you want to achieve?
The picture attached to the OP is what it is supposed to come out like. The code begins by wiping all info from "A2:I6500" with clearcontents (leaving the heading row).
The idea then is that it works out whether the student is studying more subjects or applying for more universities (and uses this information to workout where to put the separation line.)
The purpose of this is that rather than manually creating the report and giving every student 8 spaces for example, I could save trees by restricting their entry to just the required area.
I hope that makes sense
Without knowing what the input data looks like (and all of the code), I'm afraid I can't really offer any solutions as to the speed.
Hi,
Before proceeding too far down a VBA route, the question I'd ask is, is VBA even necessary? Won't standard Excel functions give you what you want?
I fear that unless you can upload the workbook, anonymised as necessary with dummy names added, then it's going to be difficult to give you an answer.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
OK.
I hope I've stripped the identifying data from the spreadsheet. So here it is (had to zip a file 'cos it's >1000kb - I think that's a lot to do with other conditional formatting etc):StudentReport.zip
The module that I'm referring to in this post is 'RunTheGrid'
Hi,
It's always difficult to 'reverse think' from someone else's code to try and understand what is actually wanted, but since the main object seems to be to gather data from the Personal sheet and summarise it on the Grid sheet, would not a Pivot Table be the answer?
It might need a rethink on the precise layout of the underlying data since at first glance there seem to be several relational links required (which of course would be better addressed by a proper relational database like Acess), but depending on the number of relations it's possible that Excel could be shoehorned into achieving this. I'd certainly be inclined, if you're going to use macros, to first consider building a single database table containing all relevant fileds which could then be used by a Pivot Table.
As you're finding there is a big time overhead when you choose to loop through every single cell in a range since VBA has to jump backwards and forwards between the VBE and the Excel Application each time.
As I say I'd consider a better table first, but you could speed things up by reading the Sheet1, Sheet2 & Sheet3 ranges in VBA arrays, and processing the arrays entirely within VB before finally writing a resultant array back to the Worksheet as a range.
OK. Thanks for input. I'd like to have tried something on Access, but no access to it on these work computers. Generally don't know enough about functions that would allow me to do this without VBA, and as I am teaching myself as I go along I am not sure how I put this stuff into an array and then back again. I guess I am in for some heavy reading....
:-)
...
but just to repeat, rather than spend your time getting the data into an array and processing you current solution much as you do now, personally I'd be inclined to write a macro or two that would bring all the relevant data into a single table that could be used by a Pivot Table. Not only will you then be able to derive your current stats. you'll also be able to dice and slice your data in ways you've not yet even thought of!
Re: Why is this code so slow?
Thanks for your input guys. I have done some reading, and although I could have gone for the pivot table option, I do not need to access this information for reporting basis - it's simply a print out for my bosses. So with the power of arrays I have significantly streamlined the code, and it completes in less than a minute. Still not rocket speed, but more than enough for what I'll be using it for.
Thank you for all your help. Here is what I ended up with:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks