删除C列单元格首个空格后文本时遇Runtime error 1004报错求助

Hey there! Let's figure out why you're hitting that Runtime Error 1004 and fix it to get exactly what you want—keeping only the text before the first space in Column C.

First, let's break down the issues in your original code:

• Excel cell indices start at 1, not 0: You used .Cells(0, 1) which is invalid—Excel doesn't have a row 0. That's one big trigger for the 1004 error.
• Wrong column target: You're working with Column 1 (A column) instead of Column 3 (C column) which is what you mentioned.
• Mismatched destination range: The TextToColumns destination is set to Range("A2"), which doesn't align with your original range and would overwrite data you probably don't want to touch.
• Brittle split logic: Using Space(99) to create a fixed-width split is risky—if any first word is longer than 99 characters, this will fail.

方法1：修正你的原逻辑（针对C列） #

This fixes the core issues and adjusts the logic to target Column C properly:

Sub keepFirstWord()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim targetRange As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Get last used row in Column C
    
    ' Set target range to Column C, from row 1 to last used row
    Set targetRange = ws.Range(ws.Cells(1, "C"), ws.Cells(lastRow, "C"))
    
    With targetRange
        ' Replace first space with a unique delimiter (pipe | here)
        .Replace What:=" ", Replacement:="|", LookAt:=xlPart, MatchCase:=False
        ' Split on the delimiter, keep only the first part
        .TextToColumns Destination:=targetRange, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Other:=True, OtherChar:="|", _
            FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlSkipColumn))
    End With
End Sub

方法2：更简洁的逐单元格处理（更直观，不易出错） #

If you prefer a approach that avoids messing with sheet structure, this loops through each cell and keeps only the text before the first space:

Sub keepFirstWord_Simple()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellText As String
    Dim spacePos As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    For i = 1 To lastRow
        cellText = ws.Cells(i, "C").Value
        spacePos = InStr(cellText, " ") ' Find position of first space
        If spacePos > 0 Then
            ws.Cells(i, "C").Value = Left(cellText, spacePos - 1) ' Keep text before space
        End If
        ' Cells with no spaces stay unchanged
    Next i
End Sub

This second method is easy to read, avoids accidental data overwrites, and gracefully handles cells that don't have any spaces.

内容的提问来源于stack exchange，提问作者Jeame