Beyond 1024 cell content limit

[Guest]

In Excel Help, it says that the...Length of cell contents (text) is limited
to 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the
formula bar.

I have a piece of VBA code that's been working fine until it ran into a cell
with more than 1024 characters. See snipet below:

(1) varRowValues = oRow (varRowValues is a Variant and oRow is a Range)
(2) oSplitRows = varRowValues (oSplitRows is a Range as well)

The idea is to copy the values of oRow into oSplitRow. However, when oRow
has a cell w/ more than 1024 characters, line (2) crashes... If I use copy
and paste it seems to work but I end up copying formating as well. I'd like
not to use copy/paste. Is there a way for this to work?

Thank you!

 

[macropod]

Hi Radon,

To avoid pasting the formatting you could use:
..PasteSpecial Paste:=xlFormulas
or
..PasteSpecial Paste:=xlValues

Cheers

 

[Dave Peterson]

I put a 10,000 character long string in A1.

Then tried this and it worked ok for me:

Option Explicit
Sub testme()

'varRowValues = oRow (varRowValues is a Variant and oRow is a Range)
'oSplitRows = varRowValues (oSplitRows is a Range as well)

Dim varRowValues As Variant
Dim oSplitRows As Range
Dim oRow As Range

Set oRow = Range("a1")
Set oSplitRows = Range("a21")

Debug.Print "oRow: " & Len(oRow.Value)

varRowValues = oRow
Debug.Print "varRowValues: " & Len(varRowValues)

oSplitRows = varRowValues
Debug.Print "oSplitRows: " & Len(oSplitRows.Value)

End Sub

I got this back in the immediate window:

oRow: 10000
varRowValues: 10000
oSplitRows: 10000

You may want to give more info about what each variable is.

 

[Guest]

I'm starting to think the issue is not Excel's limit on the cell contents. I
think it has to do w/ the some kind of limit on Variant-type
multi-dimensional arrays.

I've tried the following code:
Dim oRow1 As Range
Dim oNewRow1 As Range
Dim varValue As Variant

Set oRow1 = Range("d7", "e7") '1 row, 2 columns (colA, colB)
Set oNewRow1 = Range("d10", "e10")
varValue = oRow1
oNewRow1 = varValue

I kept adding characters to colB. I've started w/ a small number. When I got
to 912 characters in colB, the code crashed (Run-time error '1004':
Application-defined or object-defined error). I removed one character from
colB (now 911) and the code worked again. I think the issue is that the
Variant variable (varValue) funtioning as an Array may have some limitation
as to the dimension of the array. Is there a way to declare a 2-dimensional
dynamic array?

I may need to use the Copy funciton to get around this.

Thanks!

 

[Dave Peterson]

Or go cell by cell???

(Copy seems easier to me, too.)

 

[Guest]

Thanks for your help! I ended up copying cell by cell. Although the
copy/paste worked, it made the code too slow.

Regards!

 

[Dave Peterson]

That surprises me.

I would have guessed copying all the cells in the range and pasting as values
would be quicker:

Option Explicit
Sub testme()

Dim oRow1 As Range
Dim oNewRow1 As Range

Set oRow1 = Range("a1:B1")
Set oNewRow1 = Range("a11")

oRow1.Copy
oNewRow1.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub

Thanks for your help! I ended up copying cell by cell. Although the
copy/paste worked, it made the code too slow.

Regards!