Macro to clean cells of invisible spaces and trailing/leading characters

[bearcub]

I have a huge spreadsheet of 77 columns and close to 78,000 rows that I need to run the clean and trim functions to clean the data.

Is it possible to create a macro that will both clean invisible characters and leading and trailing spaces. I can't use spreadsheet functions because I have to too columns and rows. I would like to have a macro that will quickly loop through over 5M cells.

I was thinking about bringing it into PowerQuery to do this but this is an extra step. My fear is that when I am required to prepare the data for upload I will be on the clock. I need to find ways of cleaning data quickly and efficiently.

Thank you for your help in advance.

Michael

 

[alansidman]

This is untested code. With 5MM cells, I am not sure how fast this will be. Lots of data.

Sub CleanTrim()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim c As Range, rng As Range
Set rng = ActiveSheet.UsedRange
For Each c In rng
c.Value = Trim(c)
c.Value = Application.WorksheetFunction.Clean(c)
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True




End Sub

Test it on a smaller sample of data before you try it on your big data file. Then backup your big data file before you run it.

 

[Rick Rothstein]

This is untested code. With 5MM cells, I am not sure how fast this will be. Lots of data.

Sub CleanTrim()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim c As Range, rng As Range
Set rng = ActiveSheet.UsedRange
For Each c In rng
c.Value = Trim(c)
c.Value = Application.WorksheetFunction.Clean(c)
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Test it on a smaller sample of data before you try it on your big data file. Then backup your big data file before you run it.

Untested, but I do not see why the following will not do the same thing as the code you posted does...

[table="width: 500"]
[tr]
	[td]Sub Test()
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Value = Evaluate("IF(" & .Address & "="""","""",TRIM(CLEAN(" & .Address & ")))")
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

I turned off screen updating just in case, but I am not completely sure it would be needed.

 

[bearcub]

Thank you both for the help,

I tested it to trim a couple of cleans and it worked like a charm. However, I don't know how long it will take over 77 columns and 68000 rows of data. But, I'll test this when I get to work tomorrow.

Thank you again,

Michael