i have done some testing on a sheet, i used the code below to put a long string into a single cell, the entire string was visible across all the empty cells in the row. if the are any characters (even a space) in any cell in that row then the text will not show past that cell, so you need to check if any cells are not empty, even though you can see nothing, in any instance the #value did not show, just part of the text string, upto the first used cell

i thought there might be a limitation as to how many characters can go in a cell, but it would appear not or it is a very large number

code used Sheets("Sheet1").Cells(14, 1) = String(60000, "w")
at 70000 i get out of memory error

Cell Error Values


You can insert a cell error value into a cell or test the value of a cell for an error value by using the CVErr function. The cell error values can be one of the following XlCVError constants.

Constant Error number Cell error value
xlErrDiv0 2007 #DIV/0!
xlErrNA 2042 #N/A
xlErrName 2029 #NAME?
xlErrNull 2000 #NULL!
xlErrNum 2036 #NUM!
xlErrRef 2023 #REF!
xlErrValue 2015 #VALUE!