求助：Excel中定位C列首个空白单元格并删除无关行的高效VBA方案

一、精准定位C列首个空白单元格 #

你的初始代码Range("C1").End(xlDown).Offset(1, 0).Select失效的原因是：End(xlDown)会直接跳到连续非空区域的最后一行，若C列中间存在零散空白单元格，就会跳过目标位置。以下两种高效方法可精准定位首个空白单元格：

方法1：使用Find函数（最快） #

Dim firstBlank As Range
Set firstBlank = Columns("C").Find(What:="", After:=Range("C1"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)

If Not firstBlank Is Nothing Then
    ' 定位到首个空白单元格，后续操作基于此
    Debug.Print "首个空白单元格位置：" & firstBlank.Address
Else
    MsgBox "C列无空白单元格"
End If

方法2：逐行检查（兼容所有场景） #

若Find函数因特殊格式失效，可使用循环，但仅遍历到首个空白即停止，避免全列扫描：

Dim rowNum As Long
rowNum = 1
Do While Cells(rowNum, "C").Value <> ""
    rowNum = rowNum + 1
Loop
Set firstBlank = Cells(rowNum, "C")

二、高效删除非"Px Actual"行 #

针对大数据量，禁止逐行删除（会触发多次Excel重绘，效率极低），应先批量选中待删除行，再一次性删除。结合首个空白单元格的位置，操作如下：

Sub DeleteNonPxActualRows()
    ' 关闭屏幕更新、事件和计算，大幅提升速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim firstBlank As Range
    Dim lastRow As Long
    Dim deleteRange As Range
    
    ' 获取C列首个空白单元格
    Set firstBlank = Columns("C").Find(What:="", After:=Range("C1"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
    
    If firstBlank Is Nothing Then
        lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Else
        lastRow = firstBlank.Row - 1 ' 首个空白上方的最后一行数据
    End If
    
    ' 遍历目标区域，收集待删除行
    Dim i As Long
    For i = lastRow To 1 Step -1 ' 从下往上遍历，避免行号错乱
        If Cells(i, "C").Value <> "Px Actual" Then
            If deleteRange Is Nothing Then
                Set deleteRange = Rows(i)
            Else
                Set deleteRange = Union(deleteRange, Rows(i))
            End If
        End If
    Next i
    
    ' 批量删除
    If Not deleteRange Is Nothing Then
        deleteRange.Delete Shift:=xlUp
    End If
    
    ' 恢复Excel设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

三、优化重复调用 #

若需在20个公式/宏中重复使用，可将核心逻辑封装为函数，减少代码冗余：

' 获取C列首个空白单元格的函数
Function GetFirstBlankInColumnC() As Range
    Set GetFirstBlankInColumnC = Columns("C").Find(What:="", After:=Range("C1"), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
End Function

' 批量删除非指定内容行的通用函数
Sub DeleteRowsNotEqualTo(targetCol As String, targetValue As String, Optional upToRow As Long = 0)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim lastRow As Long
    Dim deleteRange As Range
    Dim i As Long
    
    If upToRow = 0 Then
        lastRow = Cells(Rows.Count, targetCol).End(xlUp).Row
    Else
        lastRow = upToRow
    End If
    
    For i = lastRow To 1 Step -1
        If Cells(i, targetCol).Value <> targetValue Then
            If deleteRange Is Nothing Then
                Set deleteRange = Rows(i)
            Else
                Set deleteRange = Union(deleteRange, Rows(i))
            End If
        End If
    Next i
    
    If Not deleteRange Is Nothing Then deleteRange.Delete Shift:=xlUp
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

' 调用示例
Sub UsageExample()
    Dim firstBlank As Range
    Set firstBlank = GetFirstBlankInColumnC()
    
    If Not firstBlank Is Nothing Then
        DeleteRowsNotEqualTo "C", "Px Actual", firstBlank.Row - 1
    Else
        DeleteRowsNotEqualTo "C", "Px Actual"
    End If
End Sub

关键优化点说明 #

1. 批量删除：通过Union收集所有待删除行，一次性删除，避免多次触发Excel界面刷新。
2. 反向遍历：从最后一行往第一行遍历，防止删除行后后续行号偏移导致漏删/误删。
3. 关闭Excel自动功能：临时关闭屏幕更新、事件触发和自动计算，大幅提升宏运行速度，完成后恢复。
4. 函数封装：将重复逻辑抽离为通用函数，便于在多个宏中调用，减少代码重复。

内容的提问来源于stack exchange，提问作者Alfredo Rodriguez Gomez