Excel:需要删除大约100万个空白行

本文关键字：100万 空白 删除 Excel | 更新日期: 2023-09-27 18:36:25

我有一个来自我们客户的 excel 文件，该文件有近 100 万行空白行。

我确实尝试在 C 列（称为 Items）上过滤它，那里它是空白的，然后选择所有行并尝试删除。它几乎冻结了将近几个小时，但最后我杀死了 excel 过程。

我也尝试了下面的 VBA 脚本，但实际行显示"r.rows（i）。删除"只是冻结。这是针对第一个删除实例本身的。

我也将 excel 文件设置为手动以进行公式计算。

我不介意整个工作花费几个小时。我可以把它留一夜，早上检查。

如果 VB.NET 或 C# 有任何内容，那也没关系。

更新1：我无法在1到1048576行之间进行批量删除，我需要删除C列为空白的行（这意味着该行为空白）。

更新2：我将以下删除标记为答案，但最终通过将GOOD行复制到另一个工作表来解决问题。

请建议处理这种情况的最佳选项。

VBA 源

Sub BlankRowDelete()
Dim r As Range, rows As Long, i As Long
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
ActiveSheet.AutoFilterMode = False
Set r = ActiveSheet.Range("A1:A1048576")
rows = r.rows.Count
For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then
        r.rows(i).Delete
        RowDeleted = RowDeleted + 1
    Else
        NotDeleted = NotDeleted + 1
    End If
    totalcnt = totalcnt + 1
    If RowDeleted = 100 Then
       TotalDeleted = TotalDeleted + RowDeleted
       RowDeleted = 0
       Debug.Print "count now is " + totalcnt
    End If
    Application.StatusBar = "Row count is " + totalcnt
Next
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
ActiveSheet.AutoFilterMode = True

结束子

Excel:需要删除大约100万个空白行

我对你的代码进行了一些更新，例如doevents，打开状态栏，因为oyu正在写入它，并在需要时更新屏幕并在写出数值时添加cstr（）。 我运行这个没有问题。如注释中所述，这只会删除单元格 A 而不是整行

Sub BlankRowDelete()
On Error GoTo myError
Dim r As Range, rows As Long, i As Long
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
ActiveSheet.AutoFilterMode = False
Set r = ActiveSheet.Range("C1:C1048576") 
rows = r.rows.Count
For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then
        r.rows(i).EntireRow.Delete
        RowDeleted = RowDeleted + 1
    Else
        NotDeleted = NotDeleted + 1
    End If
    totalcnt = totalcnt + 1
    If RowDeleted = 100 Then
       Application.ScreenUpdating = True
       TotalDeleted = TotalDeleted + RowDeleted
       RowDeleted = 0
                   'you can uncomment this but since this is a dup since writting to statusbar (imo)
      'Debug.Print "count now is " + CStr(totalcnt)
       Application.ScreenUpdating = False
    End If
    Application.StatusBar = "Row count is " + CStr(totalcnt)
    DoEvents
Next
myError:
If Err.Number <> 0 Then
MsgBox CStr(i) & ": " & Err.Description
End If
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
ActiveSheet.AutoFilterMode = True
End Sub
按

C 列升序对文档进行排序。空白行将转到末尾，因此删除它们应该很容易。

相关文章：