Home Clean & Trim Cell VBA returning #Value
Reply: 0

Clean & Trim Cell VBA returning #Value

user874 Published in April 25, 2018, 12:16 pm

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 & ")))")
Next Area

MsgBox ("Complete")
End Sub

Many Thanks

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.369032 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO