I’m using the following code to copy a row of data onto itself such that all values (including formulas results) are set to fixed values. According to ‘the book’ get/setDataArray() copies numbers and strings, but doesn’t mention comments. How can I copy & paste ‘values only, plus comments’ ?. Thanks.
Range = Sheet.getCellRangeByPosition(0, lastrow-1, 75, lastrow-1)
out = Range.getDataArray()
Range.setDataArray(out)
Record a macro to copy and paste-special values with comments.
Let your macro select the range in question and call the recorded macro.
Or record a macro for Menu / Data / Calculate / Formula To Value.
By the way, the setFormula method does not delete the cell comment, but the setFormulaArray and setDataArray methods do.
I think this is a bug (deleting a comment).
For reference. In Excel, in similar cases, comments remain alive.
Thank you @Villeroy and @sokol92 for the replies. I eventually settled on a combination of the setFormula and setDataArray methods, as I was able to split the row into 3 ranges, the middle of which only had fixed values and comments and so was workable with setFormula (I don’t need fixed values copied, only formulas and comments). The spreadsheet was migrated from Excel, so I have been used to how comments are handled there…somewhat better IMO.