Macro instead of thousands of formulas

in my spread sheet on the Today tab in column S:AE I have a number of formulas that need to be dragged down about 5000 rows when my spreadsheet is finished. When I do this the spreadsheet becomes extremely slow to the point where it does not open. Is there a way to write a macro instead of having the formulas to speed up the spreadsheet? Any help would be great!

Hi,

If the formulas are going to stay in the 'Today' tab then regardless of whether you have put them in a macro or not will not change the file size (or speed of opening). But (and I am taking a wild guess here) if you only want the information that is in the cell rather than the original formula then you could copy and paste (values only) the information to a new clean worksheet.

I know this is not the answer you were looking for but its not clear from your post how you deal with the information.

Good luck.

abousetta

Does anyone know a way to reduce the file size? Or will the file size always be extremely large when I copy the formulas down?

Two main things cause your file to grow in size: formulas and formats. As long as the formulas are in the excel sheet, the size will be large. If you need to decrease the file size, you might want to only keep one row with the formulas (if you need them again) and copy/ paste special (values only) the results that you need. If need the formulas to be active, then you might want to split your workbook into several workbooks to spread the load. Else than that, I don't see any way of decreasing the file size.

abousetta

thanks. I will make some changes to my workbook.

Sorry, its a tough situation when the file size is difficult to manage. I hope you everything works out for the best.

If you are satisfied with the responses, please mark the thread as Solved.

Good luck in your quest for knowledge.

abousetta

Try somethinh like this:
=IF(COUNTIF(B$2:B$7,B23),VLOOKUP(B23,B$2:D$7,2,0),"")
Instead of:
=IF(ISBLANK(VLOOKUP(R2,Yesterday!$A$2:$Q$6000,10,)),"",VLOOKUP(R2,Yesterday!$A$2:$Q$6000,10,))