A workbook has two sheets , sheet1 is loaded from a *.xlsx file, with more than 200 columns, sheet2 is blank. I copy a column from sheet1 to sheet2 as index column, and vlookup the columns in sheet1, each time with one column. Before vlookup a new column, the older formula is deleted. I met two problems.
Firstly, if columns number in sheet1 > 256, the workbook.CalculateAllValue() will result in an outofrange error.
Secondly, after some column vlookup operatoin, the calculation get slower and slower. It seems like the former formula is not deleted and is also calculated.
How to solve these problem? May the CalculateAllValue() be applied to a particular column instead of the whole workbook?