PDA

View Full Version : Solved: 4 letters



oleg_v
12-15-2010, 06:04 AM
hi
i need a help with a macro that will 4 first letters in the
cell in column "b" and the rest will be deleted

thanks

kroz
12-15-2010, 06:29 AM
I feel like i'm mindreading here..
Do you by any chance want a macro that would take only the first 4 letters of the string in column B?


range("B1").value = left(range("B1").value,4)

oleg_v
12-15-2010, 07:00 AM
hi
the cell needs to remane with 4 leters only
the rest should be deleted

thnks

Bob Phillips
12-15-2010, 07:39 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To Lastrow

.Cells(i, TEST_COLUMN).Value2 = Left$(.Cells(i, TEST_COLUMN).Value2, 4)
Next i
End With

Application.ScreenUpdating = True
End Sub

oleg_v
12-15-2010, 02:51 PM
Hi
thanks for the replay it all works ok
i just have one question why is it taking so long to qalqulate 1000 rows?

thanks

Bob Phillips
12-15-2010, 04:53 PM
Have you got formulae on that sheet?

Blade Hunter
12-15-2010, 04:54 PM
Try turning calculation off whilst it does its thing:


Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To Lastrow

.Cells(i, TEST_COLUMN).Value2 = Left$(.Cells(i, TEST_COLUMN).Value2, 4)
Next i
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

oleg_v
12-15-2010, 11:20 PM
Great thanks