Is there a faster way to perform this function?

[abrownin]

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]

:doh:

[mikerickson]

With Columns(13)
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, 9))
End With

[Trebor76]

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]

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]

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]

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]

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]

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.