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

 

[Weaver]

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

Insert the line

dim lr as long

I'm guessing elsewhere in your module, you have the line

option explicit

 

[milwphil]

Wow! Thank you! I tried this on some sample data and it appears to be doing what I need it to.

THANKS TO EVERYONE THAT PROVIDED SUGGESTIONS/FEEDBACK!

This forum never ceases to amaze me!

Thank you, thank you, thank you!

Phil