I have been using the below code on a workbook to clean up cells which contain a lot of text. It has worked well until recently when the content in a single cell increased significantly. In turn, instead of removing the excess spaces it instead changes the content of the cell to #value. This only happens in cells with a lot of content.
I was wondering if anyone could help shed some light on why this might be happening? I haven't been able to resolve it.
Dim rng As Range
Dim Area As Range
Dim msg As String
'Selects range to be trimmed
Set rng = ActiveSheet.Range("B14:B2500,D14:E2500,G14:K2500")
'Trim and Clean cell values
For Each Area In rng.Areas
Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")