most CPU/ RAM efficient methods for ranges

Over time, I've received incredible help from members of this forum. With that help I've built a workbook which meets my needs. (Thank you!)

Now I want to make it as CPU/RAM efficient as possible, given that my computer isn't hugely powerful.

Because I work with static .csv files, I've created a "template" workbook with multiple tabs. I paste the .csv files into a "Paste Data" tab in the template.

The other tabs in the workbook have about 35 columns with formulas and precedents, all of which are often involved in thousands of "Solver" iterations. And that's the challenge... how to recalculate all the cells as quickly as possible.

In column A of the worksheets where excel does the calculations, my formula is =if('PasteData'!A1>0,'PasteData'!A1,"")

From there, many of the other columns in theses worksheets will have formulas which begin with =if(A1="","", (rest of formula). Or sometimes I'll put the calculated # of rows in $G$3, then use =if(ROW()>$G$3,"",(rest of the formula)

So.... would changing over to dynamic range names improve calculation speed? (I've rarely used range names so I'm not familiar with their advantages/limitations).

Thanks!

You should probably look at converting your data to Structured Tables and avoid using the =IF(A1="","",...) construct completely.

Hypothetically, as I can only guess what the sheets do, I would create "empty" tables on each sheet. Use VLOOKUP to bring across the relevant data in the first row only PLUS the necessary formula for the sheet, again, only on row 1. I'd then copy the raw data in column A to each sheet. In theory, if you preselect ALL the target sheets, you'd only need to copy the data once. The Structured Tables should automatically populate all the formulae. You could automate the process by recording a macro to clear the target sheets and copy across the raw data.

You're welcome. Thanks for the rep.