Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trim multiple cells using a Do-Until loop

Posted on 2014-11-11
12
Medium Priority
?
110 Views
Last Modified: 2014-11-12
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
0
Comment
Question by:David Phelops
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40435230
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).
0
 

Author Comment

by:David Phelops
ID: 40435252
This took 10 seconds on my desktop pc
0
 

Author Comment

by:David Phelops
ID: 40435260
Excel 2010 64-bit Windows 7 2.8 gHz processor
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40435285
I even ran it with this looping code instead of your Do...Loop and it only took 1 second at the most.
     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

Open in new window


Any chance your test data has more than 1,928 rows to process (size of example) ?

-Glenn
0
 

Author Comment

by:David Phelops
ID: 40435294
I'm using exactly the same data as in the  example
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40435323
Just for grins, replace your code with this:
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

Open in new window


I've left your original loop in-place (rows 25-32), just commented it out.

-Glenn
0
 

Author Comment

by:David Phelops
ID: 40435349
Thanks, I'll that a go... Will be tomorrow morning. Cheers.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40435406
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?
0
 

Author Comment

by:David Phelops
ID: 40435420
I'm going to try it on my home pc and see if It makes a difference
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 40435461
Hi

I copied the data in approx 12000 rows and run this code which took 0 secs.

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

'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"
    
    Dim x   As String
    
    With Range("a7:e" & Range("a" & Rows.Count).End(3).Row)
        x = .Address
        .Value = Evaluate("if(len(" & x & "),trim(" & x & ")," & x & ")")
    End With

'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

Open in new window


Kris
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40435643
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
0
 

Author Closing Comment

by:David Phelops
ID: 40437056
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question