How to make each cell's length to be 5?

Question

I have a column with 7000+ names. I want to make each name's length not excess to 5. Here is what I have tried which doesn't work

Sub del()
    Dim myCell
    Set myCell = ActiveCell
    Dim count As Integer

    count = Len(ActiveCell)

    While count > 5
        myCell = Left(myCell, Len(myCell) - 1)
        myCell.Offset(1, 0).Select
        Set myCell = ActiveCell
    Wend
End Sub

user_1140579 · Answer

No need for VBA. You can use a formula, =Left(A1,5), to get the first 5 characters of the cell. Simply autofill the formula down.

If you still want VBA then also you do not need to loop. See this example. For explanation see Convert an entire range to uppercase without looping through all the cells

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    
    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            
        Set rng = .Range("A1:A" & lRow)
        rng = Evaluate("index(left(" & rng.Address(External:=True) & ",5),)")
    End With
End Sub

In the above code I am assuming that the data is in column A in Sheet1. Change as applicable.

In Action

How to make each cell's length to be 5?

1 Answers