My worksheet has values that get saved to another worksheet, which I'm copying by areas of a named range. The code looks like this:
For i = 1 to rngDest.Areas.Count
rngDest.Areas(i).Cells.Value = rngSrc.Areas(i).Cells.Value
Next i
The problem I'm having with this is that empty cells are being saved with zeroes, and when I reverse the data copying, I import a bunch of zeroes that I don't want. I know I can hide them through number formats (zeroes appear as blank) but I don't want zeroes in the cells at all.
I did have a quick fix, by using:
rngDest.Replace "0", ""
This is problematic, because it removes zeros that appear as part of a cell's value, eg, FIN01 appears as FIN1.
I realise I could change the code above by using .Text instead of .Value, but I think this can cause issues with numeric values. I can parse every cell and change a zero to "", but this slows down the process significantly. Any suggestions appreciated...
Read the documentation of the Range.Replace method
The settings for
LookAt,SearchOrder,MatchCase, andMatchByteare saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time that you use this method.
That means if you don't set these arguments it takes whatever was used last and you never know what was used last in user interface or VBA. So if you don't specify them the chance is pretty high that your code randomly works or fails.
rngDest.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=False