如何删除空行

ykcbf1100

借题练习，VBA，快速删除表中空行
常规If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then方法不行，空白行其实都是空字串

ykcbf1100

1. Sub ykcbf()   '//2025.3.28    快速删除空行
   
2.     Application.ScreenUpdating = False
   
3.     Application.Calculation = xlCalculationManual
   
4.     Dim tm: tm = Timer
   
5.     With ActiveSheet
   
6.         r = .UsedRange.Find("*", , -4163, , 1, 2).Row
   
7.         Set Rng = Nothing
   
8.         arr = .UsedRange
   
9.         For i = 2 To r
   
10.             If IsRowEmpty(.Rows(i)) Then
    
11.                 If Rng Is Nothing Then
    
12.                     Set Rng = .Rows(i)
    
13.                 Else
    
14.                     Set Rng = Union(Rng, .Rows(i))
    
15.                 End If
    
16.             End If
    
17.         Next i
    
18.         If Not Rng Is Nothing Then Rng.Delete
    
19.     End With
    
20.     Application.ScreenUpdating = True
    
21.     Application.Calculation = xlCalculationAutomatic
    
22.     MsgBox "共用时：" & Format(Timer - tm, "0.000") & "秒！"
    
23. End Sub
    
24. 
    
25. Function IsRowEmpty(rowRange As Range) As Boolean
    
26.     Dim cell As Range
    
27.     Dim isBlank As Boolean
    
28.     isBlank = True
    
29.     For Each cell In rowRange.Cells
    
30.         If Trim(cell.Value) <> "" Then
    
31.             isBlank = False
    
32.             Exit For
    
33.         End If
    
34.     Next cell
    
35.     IsRowEmpty = isBlank
    
36. End Function
    

复制代码