Error 400 when trying to operate with all used cells in Excel with VBA-CodePudding

I'm trying to take the first column from my file (all rows except header) and delete text to the left of a colon character but I get a 400 error from VBA. I don't know what's wrong with this code.

As an example A2 (and subsequent cells in the A column) look like this:

Sub cleanLoginTime()
    Dim cell As Range
    Dim MyRange As Range
    Dim tmp As String
    LastRow = Cells(Rows.Count, 1).End(xlUp)
    Set MyRange = ActiveSheet.Range("A2:A" & LastRow)  'this is your range of data
    For Each cell In MyRange.Cells
        tmp = cell.Value
        'output n - 1 characters from the right
        cell.Value = Right(tmp, Len(tmp) - 21)
    Next
End Sub

CodePudding user response：

I get a run-time error '13' because you need .row in

lastrow = Cells(Rows.Count, 1).End(xlUp).row

Do any of your cells have a length < 21?

CodePudding user response：

Remove Left From Strings in Column

Sub CleanLoginTime()
    
    Const FindString As String = ":"
    
    Dim FindStringLength As Long: FindStringLength = Len(FindString)
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range
    Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    
    Dim cell As Range
    Dim FindStringPosition As Long
    Dim CellString As String
    
    For Each cell In rg.Cells
        CellString = CStr(cell.Value)
        FindStringPosition = InStr(CellString, FindString)
        If FindStringPosition > 0 Then ' string found
            cell.Value = Right(CellString, Len(CellString) _
                - FindStringPosition - FindStringLength   1)
        'Else ' string not found; do nothing
        End If
    Next cell

End Sub

To make it more efficient (faster), you could introduce an array (Data), to access the worksheet minimally.

Sub CleanLoginTimeArray()
    
    Const FindString As String = ":"
    
    Dim FindStringLength As Long: FindStringLength = Len(FindString)
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range
    Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    
    Dim Data() As Variant: Data = rg.Value ' works only if more than one cell!
    
    Dim r As Long
    Dim FindStringPosition As Long
    Dim CellString As String
    
    For r = 1 To UBound(Data, 1)
        CellString = CStr(Data(r, 1))
        FindStringPosition = InStr(CellString, FindString)
        If FindStringPosition > 0 Then ' string found
            Data(r, 1) = Right(CellString, Len(CellString) _
                - FindStringPosition - FindStringLength   1)
        'Else ' string not found; do nothing
        End If
    Next r

    rg.Value = Data

End Sub