如果B和C列空白,我正在尝试删除excel中的整行。我有这个vba代码,如果整行为空,它将删除整行。如果B和C没有值,如何只删除行?
谢谢
Sub DeleteBlank()
Dim rng
Dim Lastrow As Integer
Set rng = Nothing
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
For Each i In Range("B1:B" & Lastrow)
If Application.CountA(i.EntireRow) = 0 Then
If rng Is Nothing Then
Set rng = i
Else
Set rng = Union(rng, i)
End If
End If
Next i
MsgBox (Lastrow)
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub
-更新-
问题已经解决了。感谢izzymo和sous2817
这是当前代码
Sub DeleteBlank()
Dim i As Integer
Dim Lastrow As Integer
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
MsgBox (Lastrow)
For i = Lastrow To 2 Step -1
If Trim(Range("B" & i).Value) = "" And Trim(Range("C" & i).Value) = "" Then
Range("B" & i).EntireRow.Select
Selection.Delete
End If
Next i
MsgBox "Done"
End Sub
根据要求,这是一种无需循环的方法:
Sub NoLoopDelete()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
With Sheet1.Range("A1:I" & lr)
.AutoFilter
.AutoFilter Field:=2, Criteria1:="="
.AutoFilter Field:=3, Criteria1:="="
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End Sub
结果应该是相同的,但是这种方式应该更快,尤其是当您有很多行时。显然,更改列引用以适合您的布局,并通过一些错误检查等来随意看它。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句