Free Microsoft Excel 2013 Quick Reference

Clean trim proper function using a macro Results

Hi Everybody,

I regularly work on large amount of data in excel for which I do the clean up everytime.
I generally use Clean,Trim and Proper functions.

Could someone please help me out with a macro which should do all the three activities with a click of button.

For Example If Column A has data for first ten thousand rows and when I select the data and run the macro all the three functions should work on that and moreover then same action is done on column B it should work Vice versa.

I am trying to incorporate a few macros to clean data files before importing them to Access. The code below is used for the trim function and it appears to run with no errors, however in checking the data it is not removing the spaces from all of the worksheet. I've tested it on a small sample worksheet with only a few entries and it works - but on a large file such as 1895 rows and 30 columns it is not working.

The record that made me aware it was not working properly is at record 1790 column 11.

Any suggestions would be greatly appreciated!

Code:
Sub trim()
Dim CountRow As Long, CountCol As Long, Sheet As Object
For CountRow = 1 To 1895
    For CountCol = 1 To 30
        Cells(CountRow, CountCol) = Application.trim(Cells(CountRow, CountCol))
    Next CountCol
Next CountRow
End Sub


I am trying to incorporate a few macros to clean data files before importing them to Access. The code below is used for the trim function and it appears to run with no errors, however in checking the data it is not removing the spaces from all of the worksheet. I've tested it on a small sample worksheet with only a few entries and it works - but on a large file such as 1895 rows and 30 columns it is not working.

The record that made me aware it was not working properly is at record 1790 column 11 - there are 2 extra spaces preceeding the text.

I thought the spaces may have been a char(160) so I used the trim function just on this one cell (row 1790 and column 11) and it worked, so I'm pretty sure it's regular spaces.

Any suggestions would be greatly appreciated!

Sub trim()
Dim CountRow As Long, CountCol As Long, Sheet As Object
For CountRow = 1 To 1895
    For CountCol = 1 To 30
        Cells(CountRow, CountCol) = Application.trim(Cells(CountRow, CountCol))
    Next CountCol
Next CountRow
End Sub



No luck finding an answer? You could always try Google.