Excel 365批量处理7.5万行数据：移除多余字符及ASCII 160的宏改造需求

嗨，Mike！我注意到你现在的宏只能处理单个单元格，要适配7.5万行的批量场景，得做针对性优化——毕竟直接循环每一行操作单元格的话，速度会慢到让人崩溃，而且还得考虑你可能没提到的常见问题，比如ASCII 160这种看不见的非断空格，很多时候也需要一并清理。

先看看你原来的代码和需求：

I found a macro that makes it good on a cell. I would like it to work on a column with 75000 rows.

Sub test() 
    Dim a$, b$, c$, i As Integer 
    a$ = Range("A1").Value 
    For i = 1 To Len(a$) 
        b$ = Mid(a$, i, 1) 
        If b$ Like "[A-Z,a-z,0-9]" Then 
            c$ = c$ & b$ 
        End If 
    Next i 
    Range("A2").Value = c$ 
End Sub

原代码的问题很明显：只固定处理A1单元格，逐字符循环拼接字符串的效率极低，而且没处理ASCII 160这类特殊空白字符。下面给你两个优化方案，都是针对批量7.5万行设计的：

方案1：用正则表达式+数组批量处理（推荐，速度最快） #

这个方案先把整列数据读到内存数组里，用正则一次性替换掉所有非字母数字的字符，同时清理ASCII 160，最后批量写回工作表，全程几乎不操作单元格，效率拉满：

Sub BatchCleanColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim i As Long
    Dim regEx As Object
    
    ' 设置要处理的工作表（这里用当前激活的表，你可以改成具体表名比如Sheet1）
    Set ws = ActiveSheet
    ' 获取A列最后一行的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' 如果没有数据就退出
    If lastRow < 1 Then Exit Sub
    
    ' 把A列数据读到数组里（从第1行到最后一行）
    dataArr = ws.Range("A1:A" & lastRow).Value
    
    ' 创建正则表达式对象
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        .Global = True ' 全局匹配
        .Pattern = "[^A-Za-z0-9]" ' 匹配非字母数字的字符
    End With
    
    ' 循环处理数组里的每一个元素
    For i = 1 To UBound(dataArr)
        If Not IsEmpty(dataArr(i, 1)) Then
            ' 先替换ASCII 160（非断空格）为普通空格，再用正则清理非字母数字
            dataArr(i, 1) = regEx.Replace(Replace(dataArr(i, 1), Chr(160), " "), "")
        End If
    Next i
    
    ' 把处理好的数组批量写回A列
    ws.Range("A1:A" & lastRow).Value = dataArr
    
    ' 释放对象
    Set regEx = Nothing
    Set ws = Nothing
    
    MsgBox "批量处理完成！共处理了" & lastRow & "行数据。"
End Sub

方案2：优化原循环逻辑（适合不想用正则的场景） #

如果不想用正则，也可以优化原有的逐字符判断逻辑，但依然用数组来减少工作表操作：

Sub BatchCleanColumnWithoutRegEx()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim i As Long, j As Integer
    Dim originalStr As String, cleanedStr As String
    Dim char As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 1 Then Exit Sub
    
    dataArr = ws.Range("A1:A" & lastRow).Value
    
    For i = 1 To UBound(dataArr)
        If Not IsEmpty(dataArr(i, 1)) Then
            originalStr = Replace(dataArr(i, 1), Chr(160), " ") ' 先清理ASCII 160
            cleanedStr = ""
            For j = 1 To Len(originalStr)
                char = Mid(originalStr, j, 1)
                ' 判断是否为字母或数字
                If char Like "[A-Za-z0-9]" Then
                    cleanedStr = cleanedStr & char
                End If
            Next j
            dataArr(i, 1) = cleanedStr
        End If
    Next i
    
    ws.Range("A1:A" & lastRow).Value = dataArr
    
    Set ws = Nothing
    MsgBox "批量处理完成！"
End Sub

使用注意事项 #

• 运行宏之前建议先备份数据，避免意外情况丢失内容；
• 如果你的数据不是在A列，把代码里的"A"改成对应列的字母即可；
• 正则方案里的[^A-Za-z0-9]表示保留字母和数字，如果你想保留其他字符（比如下划线、连字符），可以改成[^A-Za-z0-9_-]这样的格式；
• ASCII 160是网页复制过来的常见空白字符，如果你不需要处理它，可以删掉代码里的Replace(dataArr(i, 1), Chr(160), " ")这部分。

备注：内容来源于stack exchange，提问作者Mike