VBA trim and clean quick

[YounesB3]

Hello, I'm trying to clean and trim a specific range in VBA and I'm having errors. My problem is probably while setting my range, but I'm not sure why.

All my variables are defined earlier in the code:

Private LastrowMaster As Long, LastrowCT As Long
Private WS1 As String
Private MainWB As Workbook
Private rng As Range, Area As Range

Set rng = MainWB.Worksheets(WS1).Range("A" & LastrowCT & ":BP" & LastrowMaster)

'If I use this, I get an Object required error
For Each Area In rng.Areas
    Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area

'If I use this, I get a Type Mismatch error
For Each cell In rng.Cells
    cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
Next cell

What am I doing wrong?

 

[Alex Blakenburg]

And it works. Now you might say it's the same, but apparently it's not. There's probably a special space character in the first code snippets that VBA doesn't like.

Thanks for letting us know. Will have to keep this in mind in terms of future troubleshooting.
I can see one is longer but I think in the reply process the forum mechanism converted the special character into a normal space.