View Full Version : Is there a faster way to perform this function?
abrownin
05-27-2010, 09:42 PM
Hi All,
I have a program in excel which reads in a document with over 8000 lines of information and I have the following function coded to truncate the contents of one of the columns. It takes quite a while to iterate through this, and I was wondering if there's a faster way?
' truncate data in Sales Stage column
' Get rownumber of lastrow of data Col M
LastRow = Range("M15000").End(xlUp).Row
' Check row 1 to last row #, Col M
Dim pcent As Integer
For x = 2 To LastRow
pcent = (((LastRow - (LastRow - x)) / LastRow) * 100)
Application.StatusBar = "Truncating Sales Stage Column..." & pcent & "% complete"
' Cut to 7 chars
Cells(x, 13) = Left(Cells(x, 13), 4)
Application.StatusBar = False
Next x
Shred Dude
05-27-2010, 10:49 PM
:doh:
mikerickson
05-27-2010, 10:50 PM
With Columns(13)
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 9))
End With
Trebor76
05-27-2010, 10:57 PM
Hi abrownin,
Here's a possible solution - just change the helper column to suit:
Sub Macro1()
Dim lngLastRow As Long
Dim rngMyFormulas As Range, _
rngCutTo As Range
Dim strHelperCol As String
lngLastRow = Cells(Rows.Count, "M").End(xlUp).Row 'Assumes the last data row can be found from Col M. Change to suit.
strHelperCol = "N" 'Enter a free column letter.
Set rngMyFormulas = Range(strHelperCol & "2:" & strHelperCol & lngLastRow)
Set rngCutTo = Range("M2:M" & lngLastRow)
Application.ScreenUpdating = False
rngMyFormulas.Formula = "=Left(M2,4)"
'Convert formula range to values
rngMyFormulas.Value = rngMyFormulas.Value
'Cut (move) the now valued formulas from the helper column range back to the _
required destination.
rngMyFormulas.Cut rngCutTo
Application.ScreenUpdating = True
End Sub
Regards,
Robert
Shred Dude
05-27-2010, 11:21 PM
With Columns(13)
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 9))
End With
mikerickson:
Nice! I had to read the help file more than once to get that one.
Thank you. I learned something new tonight!
Trebor76
05-27-2010, 11:31 PM
Hi mikerickson,
I wouldn't mind a quick explanation!!
Hi abrownin,
Not too sure how it works, but I agree with Shred Dude in that mikerickson's solution is certainly the way to go.
abrownin
05-27-2010, 11:33 PM
You guys are impressive, I would definitely use mikerickson's solution -- I ended up coding for a slightly different purpose, so now I just find and replace and it's a snap :)
mikerickson
05-28-2010, 07:49 AM
The code I posted uses fixed width TextToColumns to split the contents of Column M at the 4th character and not import the second column.
Zack Barresse
05-28-2010, 08:34 AM
If this wasn't a situation in which TextToColumns would work, you should think about utilizing array's for things like this. They're generally much quicker when used efficiently.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.