David Phelops
asked on
Trim multiple cells using a Do-Until loop
I receive a weekly csv file from a database where several pieces of data have 3 or 4 empty spaces in a cell, depending on the database character type length.
I have set a routine to loop through 3 columns of data to trim each cell in turn (sample attached).
This routine, even with screen updating disabled is very slow.
Can you please suggest a faster method to trim the cells.
Many thanks
David
TrimCells-Sub.xlsm
I have set a routine to loop through 3 columns of data to trim each cell in turn (sample attached).
This routine, even with screen updating disabled is very slow.
Can you please suggest a faster method to trim the cells.
Many thanks
David
TrimCells-Sub.xlsm
I'm presuming that your actual source data has more spaces than this example because the macro took less than a second on my laptop (Excel 2010 32-bit, Windows 7 64-bit, 2.5GHz processor).
ASKER
This took 10 seconds on my desktop pc
ASKER
Excel 2010 64-bit Windows 7 2.8 gHz processor
I even ran it with this looping code instead of your Do...Loop and it only took 1 second at the most.
Any chance your test data has more than 1,928 rows to process (size of example) ?
-Glenn
lngLR = Range("A7").End(xlDown).Row
Set rng = Union(Range("A7:A" & lngLR), Range("C7:C" & lngLR), _
Range("E7:E" & lngLR))
For Each cl In rng
cl.Value = Trim(cl.Value)
Next cl
Any chance your test data has more than 1,928 rows to process (size of example) ?
-Glenn
ASKER
I'm using exactly the same data as in the example
Just for grins, replace your code with this:
I've left your original loop in-place (rows 25-32), just commented it out.
-Glenn
Option Explicit
Sub Trim_Cells()
'This routine trims cells in Columns 1, 3 and 5 using a Do-Until Loop
'Declare Variables
Dim tStart As Date
Dim tFinish As Date
Dim tRunTime As Double
Dim R As Integer 'Row Number
Dim lngLR As Long 'last row
Dim rng As Range
Dim cl As Object
'Switch off screen refresh
Application.ScreenUpdating = 0
'Record Start Time
tStart = Now()
'Set user message in the staus bar
Application.StatusBar = "Trimming Vendor Codes. Please be patient"
'R = 7 'First data row
'Loop through all job records
'Do Until Cells(R, 5) = "" 'Test for empty cell
' Cells(R, 1) = Trim(Cells(R, 1)) 'Trim Client_Code
' Cells(R, 3) = Trim(Cells(R, 3)) 'Trim Vendor_Code
' Cells(R, 5) = Trim(Cells(R, 5)) 'Trim Booking Ref
' R = R + 1
'Loop
'Alternate looping code
lngLR = Range("A7").End(xlDown).Row
Set rng = Union(Range("A7:A" & lngLR), Range("C7:C" & lngLR), _
Range("E7:E" & lngLR))
For Each cl In rng
cl.Value = Trim(cl.Value)
Next cl
'Record the finish time
tFinish = Now()
'Reset status bar message
Application.StatusBar = False
'Calculate and show time taken for routine to run.
tRunTime = (tFinish - tStart) * 24 * 60 * 60
tRunTime = MsgBox("Time taken for routine is " & Round(tRunTime, 5) & " seconds", , "Run Time")
End Sub
I've left your original loop in-place (rows 25-32), just commented it out.
-Glenn
ASKER
Thanks, I'll that a go... Will be tomorrow morning. Cheers.
When I ran the code it said it took 0 seconds so I changed the timing method to use GetTickCount as described in this article of mine and it showed that it took just 187 ms. (Excel 2010 32 bit in Windows XP running as virtual machine on a MAC). Have you checked to see how much free memory you have?
ASKER
I'm going to try it on my home pc and see if It makes a difference
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does your real data have calculations associated to the values which are being trimmed?
If so try turning to Manual Calculation before running the macro.
If that helps the switch to manual calc (and back to auto) can be included in the routine.
Thanks
Rob H
If so try turning to Manual Calculation before running the macro.
If that helps the switch to manual calc (and back to auto) can be included in the routine.
Thanks
Rob H
ASKER
Thanks for all your help and comments:
Kris - brilliant - I don't quite understand (yet) how your code works, but it certainly does - it ran at 0 seconds
Glenn - thanks, but your code also ran at 10 seconds
Rob - no in this case there are no calculations. This is a text-only set of values.
Martin - I have plenty of free memory: I restarted the pc and ran the code with nothing else open.
Kris - brilliant - I don't quite understand (yet) how your code works, but it certainly does - it ran at 0 seconds
Glenn - thanks, but your code also ran at 10 seconds
Rob - no in this case there are no calculations. This is a text-only set of values.
Martin - I have plenty of free memory: I restarted the pc and ran the code with nothing else open.