Clean and Proper difficulties

[stranno]

In order to clean data i often use:
rng.Value = Evaluate("IF(ROW(" & rng.Address & "),TRIM(" & rng.Address & "))"). But lately i found that this code affects the data if the autofilter is active.

Take a look at the attached workbook. hide for instance number 8 by using the autofilter and push de botton. The result is not particularly desirable.

What is the best way to clean and proper data fast.

[mancubus]

you may add below before evaluate...

ActiveSheet.AutoFilterMode = False

[stranno]

Yes i know. But that's not what i want. Users sometimes have filtered the data in many ways on different fields and criteria. How can these settings be restored? I know a way to do this, and sometimes i used it too. But it can only be applied on relative simple Filters in Excel 2003 and before. Excel 2007 and 2010 have more advanced filter possibilities. Is there any code available to store and restore all these different criteria?

[shrivallabha]

Maybe this:
Sub Clean_Proper()
Dim rng As Range, r As Range
Set rng = ActiveSheet.Range("A2:A20")
For Each r In rng
r.Value = Evaluate("IF(ROW(" & r.Address & "),TRIM(" & r.Address & "))")
Next r
End Sub

[stranno]

Hi Shrivallabha,

hanks for your comment.
But i wonder if this approach is suitable
for a large database (up to 50000 records or more). I will check this first, after that i will get back on this issue.

regards,
Stranno

[stranno]

Hi Shrivallabha,

The method to trim a large spreadsheet cell after cell is unfortunately too slow.
Do you know why the use of an autofilter prevents to trim properly?
Regards,
Stranno

[shrivallabha]

I really do not know about such nuances of the application. I am sorry. However, try the following workaround which loops only on the visible cells so with filters being set to "ON" it will be little faster than the previous code.

Sub Clean_Proper()
Dim rng As Range, r As Range
Set rng = ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set rng = rng.SpecialCells(xlCellTypeVisible)
For Each r In rng
r.Value = Evaluate("IF(ROW(" & r.Address & "),TRIM(" & r.Address & "))")
Next r
End Sub

[stranno]

Yes, I think that's the best way to handle this.
thanks.

[frank_m]

Feel free to correct me if I'm wrong, but from looking at this link:
http://www.excelforum.com/excel-programming/721973-trim-a-range-of-cells-with-evaluate.html

It's my understanding that the Evaluate("IF(ROW(" ... is used to work with your entire range as an array, as that is much quicker than looping.

In other words I believe that if your going to iterate thru the range cell by cell, you only need r.Value = Trim(r.Value)

....
For Each r In rng
r.Value = Trim(r.Value)
Next r