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?

 

[YounesB3]

Did you try the change I suggested for Area in post #8 ?

I removed the Option Explicit. It says object required. As if the variable wasn't defined. I don't get it.

Do range variable need to use the "set" always?

 

[Alex Blakenburg]

I removed the Option Explicit. It says object required. As if the variable wasn't defined. I don't get it.

Do range variable need to use the "set" always?

Normally yes but "For Each <object> in <object_collection>
would be the exception

Can you replace your for each loop with this one and tell us what you see in the immediate window.

Debug.Print rng.Address
For Each Area In rng.Areas
    Debug.Print Area.Address
    Area.Value = Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")
Next Area

Also where do you have your code ? In a Sheet module or a normal module ?
Any reason you are delclaring your variables at the module level and not inside the actual procedure (ie Sub) ?

 

[MARK858]

Any reason you are delclaring your variables at the module level and not inside the actual procedure (ie Sub) ?

Because they are Private declarations not Dim

 

[MARK858]

Any reason you are delclaring your variables at the module level and not inside the actual procedure (ie Sub) ?

Because they are Private declarations not Dim

@Alex Blakenburg , try running the first code below (the code in post 5) and then try running the 2nd code below.
You just need a sheet called ABC, you don't need anything in the cells to show the difference in how the 2 codes act.

Option Explicit

    Private WS1 As String
    Private MainWB As Workbook
    Private rng As Range, cell As Range
Sub TEST()
    Set MainWB = ThisWorkbook
    WS1 = "ABC"
    Set rng = MainWB.Worksheets(WS1).Range("A3000:BP3020")

 
    For Each cell In rng.Cells
        cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
    Next cell
End Sub

Option Explicit


Sub TEST2()
    Private WS1 As String
    Private MainWB As Workbook
    Private rng As Range, cell As Range

    Set MainWB = ThisWorkbook
    WS1 = "ABC"
    Set rng = MainWB.Worksheets(WS1).Range("A3000:BP3020")

 
    For Each cell In rng.Cells
        cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
    Next cell
End Sub

 

[YounesB3]

Hi, I replaced this

For Each Area In rng.Areas
   Area.Value = Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")
Next Area

With this

For Each Area In rng.Areas
    Area.Value = Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")
Next Area

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.

Just a quick question about the Evaluate thing which I'm not familiar. Can you explain how it works? I tried to read about it and it's not clear, but really more efficient then looping through cells which I would have normally done. Also, could you explain what you changed?

From this

Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")

To this

Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")

Thanks for your help both of you!

 

[MARK858]

Also, could you explain what you changed?

The important thing Alex changed is the red 1 in the below which changes it to External = True which means it references the book and sheet of the range being used, it defaults to False which references the ActiveSheet

Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")

the below will probably also work for you (although the above is better IMO as it specifies the parameters rather than relying on the defaults)

Evaluate("IF(ROW(" & Area.Address(External:=True) & "),CLEAN(TRIM(" & Area.Address(External:=True) & ")))")

If you put the Address in the Immediate window you get the results below which shows the difference

?Sheets("ABC").Range("a3000").address
$A$3000

?Sheets("ABC").Range("a3000").address(external:=true)
[Book1.xlsb]ABC!$A$3000

 

[YounesB3]

Great, that's what I actually needed and the code would have crashed since I didn't test for that (and it's running from another tab).

What about the evaluate function, what does it do exactly?

 

[MARK858]

What about the evaluate function, what does it do exactly?

That is a bit of a huge subject but in simple terms it converts Names to objects or values

 

[YounesB3]

Ok thanks!

 

[MARK858]

You're welcome (although I think that you should mark post 8 by Alex as the solution as that is what finally got the end result that you were looking for, I just helped explain it)