4

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
New
  • 49
  • 1
  • 8

1 Answers1

5

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.

enter image description here

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

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    I wonder how `Evaluate` performs compared to a loop over an array. Any experiences? – Pᴇʜ Jul 23 '21 at 05:47
  • 2
    I did a quick test. My Test Results on `A1:A1048576` **1.** `Evaluate` and `Array` both took 4 seconds. So I would prefer `Evaluate` in this scenario because of lesser lines of code. **2.** Looping through the range took 1 minute and 50 seconds. – Siddharth Rout Jul 23 '21 at 06:10
  • 1
    Amazing job. Well looping the range was clear to be the longest ^^ But good to know that `Evaluate` performs so good, because I like how easy it is to do that task with `Evaluate`. – Pᴇʜ Jul 23 '21 at 06:28
  • Helpful to discover this interesting time behaviour, possibly version dependant? - Friendly hint: using VBA I'd urgently recommend to *fully qualify* even the range address string via `External:=True` argument as follows: `rng = Evaluate("index(left(" & rng.Address(External:=True) & ",5),)")` @SiddharthRout – T.M. Jul 25 '21 at 10:15
  • 1
    @T.M. Yes it s a good suggestion. I have updated the above post :) – Siddharth Rout Jul 25 '21 at 10:21