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

 

[Joe4]

You are missing your lastrow calculation.

If you are trying to find the lastrow with data in column C, then add this line under your "With Ws" line:

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

 

[DanteAmor]

Note: Data must start on row 21

Try this:

Sub CopyPaste()
  Dim i As Long

  Application.ScreenUpdating = False
 
  With ActiveWorkbook.Sheets("Summary by L5")
    For i = 21 To .Range("C" & Rows.Count).End(3).Row
      .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
    Next i
  End With
 
  Application.ScreenUpdating = True
End Sub

 

[Dokat]

You are missing your lastrow calculation.

If you are trying to find the lastrow with data in column C, then add this line under your "With Ws" line:

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

Thank you. I added however its giving me run time error 5 invalid procedure error. Below is the updated code.

Sub Deletelast6()

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

    lastrow = .Cells(.Rows.Count, 3).End(xlUp).Row
    For i = 2 To lastrow
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
    Next i
End With
    
Application.ScreenUpdating = True

End Sub

 

[Joe4]

Well, you should really declare all your variable first, like this:

Sub Deletelast6()

Application.ScreenUpdating = False

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

With Ws

    lastrow = .Cells(.Rows.Count, 3).End(xlUp).Row
    For i = 2 To lastrow
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
    Next i
End With
  
Application.ScreenUpdating = True

End Sub

though that still shouldn't return that error.

When you get that error message and click "Debug", which line of code does it highlight?

 

[DanteAmor]

I added however its giving me run time error 5 invalid procedure error. Below is the updated code.

When the cell has less than 6 characters you can't remove

Try:

Sub CopyPaste()
  Dim i As Long

  Application.ScreenUpdating = False
  
  With ActiveWorkbook.Sheets("Summary by L5")
    For i = 21 To .Range("C" & Rows.Count).End(3).Row
      If Len(.Cells(i, 3)) > 6 Then
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
      End If
    Next i
  End With
  
  Application.ScreenUpdating = True
End Sub

 

[Dokat]

When the cell has less than 6 characters you can't remove

Try:

Sub CopyPaste()
  Dim i As Long

  Application.ScreenUpdating = False
 
  With ActiveWorkbook.Sheets("Summary by L5")
    For i = 21 To .Range("C" & Rows.Count).End(3).Row
      If Len(.Cells(i, 3)) > 6 Then
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
      End If
    Next i
  End With
 
  Application.ScreenUpdating = True
End Sub

Thank you!. This worked perfectly but what i am confused about it all cells has more than characters.

 

[Dokat]

Well, you should really declare all your variable first, like this:

Sub Deletelast6()

Application.ScreenUpdating = False

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

With Ws

    lastrow = .Cells(.Rows.Count, 3).End(xlUp).Row
    For i = 2 To lastrow
        .Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)
    Next i
End With
 
Application.ScreenUpdating = True

End Sub

though that still shouldn't return that error.

When you get that error message and click "Debug", which line of code does it highlight?

I did. but still had the same issue. DanteAmors solution worked. Thanks

 

[Joe4]

I did. but still had the same issue. DanteAmors solution worked. Thanks

OK. It looks like the issue was then that you had some entries that were less than 6 characters, so it is not possible!
Of course, there is no way for us to know that if you have not shown us a sample of the data you are trying to run this against.
Those details keep tripping you up!

 

[DanteAmor]

This worked perfectly but what i am confused about it all cells has more than characters.

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.

.Cells(i, 3) = Left(.Cells(i, 3).Value, Len(.Cells(i, 3).Value) - 6)

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