Left Len function with VBA

[Dokat]

Hi,

I have a table where i would like to delete last 6 character in each row on column C.

I am using below code to delete last 6 characters basically left Len function. When I ran it doesn't give me an error however it doesn't do anything. Can nayone help me with this issue? Thanks

Sub CopyPaste()

Application.ScreenUpdating = False

    Dim Wb As Workbook
    Dim Ws As Worksheet
    Set Wb = ActiveWorkbook
    Set Ws = Sheets("Summary by L5")
    Wb.Activate
    Ws.Select

With Ws
    For i = 21 To lastrow
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
    Next i
End With
    
Application.ScreenUpdating = True

 

[Dokat]

In a column to the right put this formula:

=Len(C21) and copies it down.
Check with autofilter which cells have less than 6 characters.


---------------------
The error is because when performing the subtraction the result is negative and the Left function does not accept negatives.


Exmaple
.Cells(i, 3) = Left(amor, Len(amor) - 6)
.Cells(i, 3) = Left(amor, 4 - 6)
.Cells(i, 3) = Left(amor, -2) 'error

I think what's happening is there are 2 blank rows on the bottom of the rows that's what causing the issue