EXPERT CHALLENGE: Cleaning Data By Copy The Range Into An Array

[milwphil]

Good morning!

I thought I had this issue resolved but the worksheet is reacting fairly slow and I'm not able to manipulate it the way I'd like to (the code requires that you select the cells and then run the macro).

Subsequently, I was told that I could "scrub" data by copying the range into an array, clean the text (removing any trailing spaces) in the array and then assign the array back to the spreadsheet.


Sounds easy for someone that's an expert, but for me--I'm struggling to understand how to make it happen.


To make matters worse, the range will have a lastRow that will vary pending on the data I'm using.


I mercifully beg to the collective wisdom of Mr. Excel for any suggestions/solutions.


Feedback is greatly appreciated!


Thanks,
Phil

 

[CharlesChuckieCharles]

Good morning!

I thought I had this issue resolved but the worksheet is reacting fairly slow and I'm not able to manipulate it the way I'd like to (the code requires that you select the cells and then run the macro).

Subsequently, I was told that I could "scrub" data by copying the range into an array, clean the text (removing any trailing spaces) in the array and then assign the array back to the spreadsheet.


Sounds easy for someone that's an expert, but for me--I'm struggling to understand how to make it happen.


To make matters worse, the range will have a lastRow that will vary pending on the data I'm using.


I mercifully beg to the collective wisdom of Mr. Excel for any suggestions/solutions.


Feedback is greatly appreciated!


Thanks,
Phil

Try this

Public Sub CleanofSpaces()
Dim c As Range
For Each c In Selection
    If Not IsNumeric(c.Value) Then
    
        c.Value = Trim(c.Value)
    End If
    
Next c
End Sub

Uses a selection

clears off leading and trailing spaces

 

[milwphil]

Thanks for the quick response! How would I auto define the "Selection?"

For example, I know the range will start at C11 thru column Z, but the last row will vary?

I'd like to be able to automate the "Selection" range so the cleaning of the data is seemless.

Thanks again!
Phil

 

[CharlesChuckieCharles]

Public Sub CleanofSpaces()
Dim c As Range
ActiveSheet.UsedRange.Select
For Each c In Selection
    If Not IsNumeric(c.Value) Then
 
        c.Value = Trim(c.Value)
    End If
 
Next c
End Sub

This should work everywhere on the active work sheet (which ever that one is at the time)

 

[milwphil]

For some reason, I keep getting a debug error at line:

c.Value = Trim(c.Value)

It doesn't seem to like my drop down values that are above header row.

Thanks again for your willingness to help!

 

[MrKowz]

Milwphil,

What column could be referred to that has data extending down to the last row of the data, and what row could be referred to that has data extending to the far right of the data? Knowing this, we can dynamically determine what the last row and last column of your data is. Then, we can loop through this range and apply the trim function to all non-numeric values.

 

[CharlesChuckieCharles]

Public Sub CleanofSpaces()
Dim c As Range
ActiveSheet.UsedRange.Select
For Each c In Selection
    If Not IsNumeric(c.Value) Then
 
        c.Value = Trim(c.Value)
    End If
 
Next c
End Sub

This should work everywhere on the active work sheet (which ever that one is at the time)

Public Sub CleanofSpaces()
Dim c As Range
ActiveSheet.UsedRange.Select
For Each c In Selection
    If (Not IsNumeric(c.Value)) And (Not c.HasFormula) Then
    
        c.Value = Trim(c.Value)
    End If
    
Next c
End Sub

(Not c.HasFormula) should stop that

 

[Weaver]

As a general principle, if execution speed is an issue, avoid using 'selection' and turn off screen updating

Public Sub CleanofSpaces()
Dim c As Range
application.screenupdating=false
For Each c In ActiveSheet.UsedRange
    If Not IsNumeric(c.Value) Then
 
        c.Value = Trim(c.Value)
    End If
 
Next c
End Sub

If there's some data you don't want to process, then be more specific with the range

Public Sub CleanofSpaces()
Dim c As Range
application.screenupdating=false
lr = cells(rows.count,"C").end(xlup).row 'finds last data item in col C
For Each c In range("C11:Z" & lr)
    If Not IsNumeric(c.Value) Then
 
        c.Value = Trim(c.Value)
    End If
 
Next c
End Sub

Hope this helps.

 

[milwphil]

Wow! Thank you for all of the responses! I think I'm closer, but I'm still getting debug errors.

CharlesChuckieCharles, with your last suggestion I received a 400 error. I'm not sure what that means.

Weaver, I tried your 2nd suggested code and there was a debug error stating "lr" was not defined.

MrKowz, If I'm understanding your question correctly. The column that has the data is C, but the data starts at row 11. The end column is Z and could extend to about 1000 rows (so Z1000). The data could vary from row 11 to row 1000 (or even further).
Any further suggestions?

I'm more than appreciative for your feedback already!

Thanks again!
Phil

 

[MrKowz]

Try:

Public Sub TrimAllValues()
Dim rng As Range, _
    LR  As Long, _
    LC  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("C" & Rows.Count).End(xlUp).Row
LC = 26
For Each rng In Range(Cells(11, 3), Cells(LR, LC))
    If Not IsNumeric(rng.Value) And Not rng.HasFormula Then
        rng.Value = Trim$(rng.Value)
    End If
Next rng
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub