Goal : I need to remove all background color in all cells before my validation function which will highlight the cell with different color if the cell value is not acceptable.
Problem : after the line below is run
Range("A1:BS65535").Interior.ColorIndex = xlColorIndexNone
It seems that many empty cells have been generated in the Excel file and the file become around 12 MB from few hundreds Kb. I am using Excel 2016 and I have created a brand new excel with just a button which trigger the line listed above.
However, I have another old excel file (which created by 2007 / 2010 excel long time ago). In that Excel VBA, there is exactly the same line as i mentioned above. And, when i save that Old excel after that line is run, it seems that that Excel will remove all empty cells automatically and the file size does not have much change.
Is there any setting I missed when preparing my Excel File?
Try applying it to just the used range instead. This should prevent the expansion of the used range which increases the file size.
ActiveSheet.Range("A2",Cells.SpecialCells(xlCellTypeLastCell)).Interior.ColorIndex = xlColorIndexNone
This will exclude the first row, but note that finding the "Last Cell" sometimes has issues depending on usage. In this case, I think it should be fine.