Im trying to run a function that TRIM and CLEAN all cells in a range, using a worksheetfunction. However this takes forever since I loop trough all cells.
In order to test I have set range = A1:X100000, however in actual use this range may vary, and be up to 3-4 times bigger.
My current code now takes about 16-17 minutes, is there any better way to do what I want that improves the speed?
Please Login or Register to view this content.
Try this...
It reads the the cell range values into a variant array then clean-trims the array then writes the array back to the cells. It may seem counter-intuitive, but it is much faster than doing it one cell at a time.Please Login or Register to view this content.
Also, technically this is not a Function. It's a procedure. Functions return a value back to the caller.
Try specialcells.
Please Login or Register to view this content.
Kind regards, Harry.
Hello TommyN,
Welcome to the Forum.
See if this speeds up the process:
and at the end;Please Login or Register to view this content.
Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
@HSV,
What exactly does SpecialCells(2) do? It seems to improve speed a lot when there is little/no data in my range, but not a big improvement if cells contain data. Does it skip empty cells?
Last edited by jeffreybrown; 12-08-2012 at 02:55 PM.
That's right.Originally Posted by TommyN
Hello TommyN,
The quickest way is copy the cell data into an array. This greatly reduces the overhead time by eliminating the processing to retrieve a Range object.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
@: Leith Ross and AlphaFrogs
Both your codes made quite a big difference in performance (AlphaFrogs: 3.02 minutes, Leith Ross: 47 seconds)
But none of your codes seems to actually TRIM or CLEAN the cells in the range, like my original code did.
Hello TommyN,
In that case, you should upload a copy of the workbook for review.
try
Please Login or Register to view this content.
@jindon,
How do I get this to reference to the sheet I want to trim/clean?
@Leith Ross,
I made a new workbook for testing purposes, with some test data and narrowed the range.
As you can see, if you try to run the 3 different subs, only the first one will actually trim the extra spaces from the text, leaving just one space between each word and no spaces in front or at the end.
Something like
Please Login or Register to view this content.
@jindon: thank you, this works and improves the speed at about 2 minutes compared to my original 17. Even though I don't understand what your code actually does.
However, Im wondering if this is the most efficient way, or are there still room for improvement? Because as I said my range may be up to 4 times bigger, and I would guess that this would take 4 times longer?
Leith Ross code took about half the time, but since it didnt' do anything I cant use it. Does someone knows whats wrong? Or know of other efficient ways?
Not sure if there is faster solution.
Regarding Leith Ross code
You can't change the elements in an array by For Each loop.
This will loop through each element in the array.
Also note; WorksheetFunction.Trim is not exactly the same as VBA function Trim.Please Login or Register to view this content.
WorksheetFunction.Trim will trim multiple consecutive spaces within the text.
VBA Trim will only trim leading and trailing spaces.
Example (periods represent space characters for illustration):
WorksheetFunction.Trim "..Test....Test.." becomes "Test.Test"
VBA Function Trim "..Test....Test.." becomes "Test....Test"
I am curious to know if the code below speeds up the process.
I borrowed code from jindon. I am going on the idea that the
more data you process at one time, the more it will impact
computer memory and increase the time to process, possibly reaching
a point where it takes minutes rather than seconds to complete.
With your data in every cell from A to X in 65,500 rows, the macro
took 8 seconds. The difference is that I break the data into
3 blocks and the results are entered in Sheet2.
Please Login or Register to view this content.
Last edited by xLJer; 12-11-2012 at 11:54 PM.
See the difference.
Only about one second on my PC.
And the resultsPlease Login or Register to view this content.
test1 14.40625
test2 13.01953
test1 14.69922
test2 13.05469
test2 is faster.
Last edited by jindon; 12-12-2012 at 12:07 AM.
Hi-
For me, jindon's solution is the best but even further improved as below unless I am doing something wrong.
jindon, would calling once the evaluate boost the speed as below?
atleast in my PC I got the same data results and took 3.04688 seconds
Regards,Please Login or Register to view this content.
Event
Please Login or Register to view this content.
ThanksI went with jindons last solution where "test2" was fastest. It was far superior than anything else I tried. Still uses some time (17-18 seconds), but at least now users won't wait forever...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks