• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Effects of Combing Multiple Worksheets To 1 Worksheet

I

indi visual

Member
Would my workbook become slower or faster if I consolidated everything to one sheet.


Same amount of data, just all on one of sheet instead.


I am debating condensing, but I wanted to know if this would actually slow up all of my code, make it faster, or would it even matter?
 
Depends on what it is, and what you're doing with it. If it's just static data, it probably doesn't matter. If you're sorting/filtering it, that can cause XL to flag those cells as "drity" and needing recalculation. In which case, causing them all to be on 1 sheet and all needing to be re-calculated could be detrimental.


Code wise, you're going to improve speed if you can cut down the number of times the VB reads/writes to the workbook, and/or cut down the actual lines. Again, this can be very case-specific.


On the other hand, there's something to be said for having a well organized workbook with everything put in a nice spot so it's easy for someone else (replacement when you get a better job?) to use the workbook.


You could always try doing a small bit first, maybe condense just two sheet, see if it helps.
 
Good stuff.


I've placed two blank columns between every other column in one of my workbooks (just in case I need to slide some data in somewhere eventually).


Will this slow me up (or since they're blank columns does it even matter)?
 
Doesn't matter.


One additional note: if you're trying to speed up calculation time, remember that XL starts at A1 and goes across then down. So, putting cells at the top that are dependent on cells at the bottom which are dependent on something else causes a "slight" slowdown in calculation.
 
Very very interesting...


So you mean Excel will read my data from A1 to Z1.. drop down to the next line, and then calculate B2 to Z2 so on and so forth?
 
I think so. At least, that's what I seem to notice from personal experience. Here's a page with several more "excel optimization" ideas:

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
 
You must log in or register to reply here.
Back
Top