right function

[Phil_Pearce]

I would like to loop through all cells in column B6 onwards and change the cell so there are only 9 characters counting from the right. I would like to use the right function but i can not get it to loop through properly. Please help

 

[Andrew Poulsom]

Maybe try:

Sub Test()
    Dim LastRow As Long
    Dim Cell As Range
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For Each Cell In Range("B6:B" & LastRow)
        With Cell
            If Len(.Value) > 9 Then
                .Value = Right(.Value, 9)
            End If
        End With
    Next Cell
End Sub

 

[Phil_Pearce]

That worked a treat.

Regards

Phil