Is this what you mean
Code:Sub stuff() Dim c As Range Dim d As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ActiveSheet .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).Copy .Range("C2") Set ws = ActiveWorkbook.Sheets("CMRData") For Each c In .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp)) If Not IsNumeric(c.Offset(0, 3).Value) Then If Len(c.Value) = 6 Or Len(c.Value) = 5 Then c.Value = Left(c.Value, 5) End If Else c.Value = "" End If Next c End With Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
Bookmarks