如何将多个非相邻列转换为小写字母？10k行数据循环处理提速的非数组方案咨询

兄弟，你这段代码慢的核心原因太典型了——逐单元格和Excel对象模型反复交互，1万行×12列就是12万次读写操作，每一次都要触发Excel的界面刷新和对象调用，速度能快才怪！除了用数组，这几个方法能大幅提升处理速度：

方法1：用Evaluate批量计算，把N次交互压缩成1次 #

Evaluate可以一次性对整列数据执行LOWER函数，直接批量赋值，把每列的处理从逐单元格变成一次读写操作，效率提升非常明显：

Sub ConvertToLower_Evaluate()
    Dim ws As Worksheet
    Dim targetCols As Variant
    Dim col As Variant
    Dim lastRow As Long
    Dim dataRange As Range
    
    ' 先开提速buff（必加！）
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' 定义需要处理的列，避免重复写代码
    targetCols = Array("B", "D", "I", "J", "L", "M", "N", "P", "Q", "R", "Z", "AA")
    
    ' 处理第一个工作表 spbe30
    Set ws = spbe30
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' 以B列为参考找最后一行
    For Each col In targetCols
        Set dataRange = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
        ' 批量执行转小写并赋值
        dataRange.Value = ws.Evaluate("LOWER(" & dataRange.Address & ")")
    Next col
    
    ' 处理第二个工作表 spbe60
    Set ws = spbe60
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For Each col In targetCols
        Set dataRange = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
        dataRange.Value = ws.Evaluate("LOWER(" & dataRange.Address & ")")
    Next col
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

这个方案把12万次交互压缩成24次（两个工作表×12列），速度至少能提升几十倍。

方法2：用SpecialCells只处理非空文本单元格 #

如果你的目标列里有大量空单元格，用SpecialCells筛选出仅文本型非空单元格，减少不必要的处理范围，速度会更上一层楼：

Sub ConvertToLower_SpecialCells()
    Dim ws As Worksheet
    Dim targetCols As Variant
    Dim col As Variant
    Dim lastRow As Long
    Dim dataRange As Range
    Dim textCells As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    targetCols = Array("B", "D", "I", "J", "L", "M", "N", "P", "Q", "R", "Z", "AA")
    
    ' 处理spbe30
    Set ws = spbe30
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For Each col In targetCols
        Set dataRange = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
        ' 捕获仅文本型常量单元格（跳过空值和公式）
        On Error Resume Next ' 防止没有符合条件的单元格报错
        Set textCells = dataRange.SpecialCells(xlCellTypeConstants, xlTextValues)
        On Error GoTo 0
        If Not textCells Is Nothing Then
            textCells.Value = ws.Evaluate("LOWER(" & textCells.Address & ")")
            Set textCells = Nothing
        End If
    Next col
    
    ' 处理spbe60
    Set ws = spbe60
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For Each col In targetCols
        Set dataRange = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
        On Error Resume Next
        Set textCells = dataRange.SpecialCells(xlCellTypeConstants, xlTextValues)
        On Error GoTo 0
        If Not textCells Is Nothing Then
            textCells.Value = ws.Evaluate("LOWER(" & textCells.Address & ")")
            Set textCells = Nothing
        End If
    Next col
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

如果你的空单元格占比超过50%，这个方法比方法1还要快不少。

额外提醒：基础提速开关必须加 #

不管用哪种方法，Application.ScreenUpdating = False和Application.Calculation = xlCalculationManual这两行一定要加——关闭屏幕刷新和手动计算能避免Excel在处理过程中反复刷新界面、重算公式，这也是提速的关键。

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