Remove Characters after certain # of characters reached

[MatthewLane412]

Hello,

I am trying to remove all character in column AK that exceed 14 character in length. I found some code online which works for column A only. I have been working on trying to get it to work with Column AK.

lastrow= Range ("A20000").End(xlUp)Row

For I = 1 To lastrow

If Len(Cells(I, 1)) >14 Then

Cells (I, 1) = Left (Cells (I, 1), 14)
End If

Next I



I was thinking code similar to this could work but I had no success.

Dim finRow As a String
finRow = Sheets("Data").Range("A20000").End(xlUp).Row

Set myRange = Sheets ("Data").Range (AK2:AK" & finRow)

Thank you for taking a look.

 

[leeavery3]

Finding the last row you should you rows.count. this way you can insure you start from the very last row in your column.

AK is column 37

lastrow = Cells(Rows.Count, 37).End(xlUp).Row


For I = 1 To lastrow
If Len(Cells(I, 37)) > 14 Then
Cells(I, 37) = Left(Cells(I, 37), 14)
End If
Next I

 

[MatthewLane412]

Took me directly to column AK. Thanks for the tip.

Matt

 

[Fluff]

Another option, without the loop

Sub MatthewLane412()
With Range("AK2", Range("AK" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("if(@<>"""",left(@,14),"""")", "@", .Address))
End With
End Sub