如果列B和C都为空，Excel VBA将删除整行 [英] Excel VBA delete entire row if both columns B and C are blank

问题描述

如果列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 
 
对于每个我在范围内（B1：B& Lastrow）
如果Application.CountA（i.EntireRow） = 0然后
 
如果rng不是，然后
设置rng = i 
 Else 
设置rng = Union（rng，i）
 End If 
 End If 
 Next i 
 MsgBox（Lastrow）
如果不是rng是没有，然后
 rng.EntireRow.Delete 
如果
 
 End Sub 
  

- 更新 -

问题解决了。感谢izzymo和sous2817

以下是当前代码

 子DeleteBlank（）
 
 Dim i As Integer 
 Dim Lastrow As Integer 
 
 Lastrow = Range（A& Rows.Count）.End（xlUp）。行
 MsgBox（Lastrow）
对于i = Lastrow到2 Step -1 
如果Trim（Range（B& i）.Value）=和Trim（Range（ C& i）.Value）=然后
 Range（B& i）.EntireRow.Select 
 Selection.Delete 
 
 End If 
 
 Next i 
 
 MsgBoxDone
 
 End Sub 
  

解决方案

根据要求，这里是一种无循环的方式：

  Sub NoLoopDelete（）
 Dim lr As Long 
 
 lr = Range（A& Rows.Count）.End（xlUp）.Row 
使用Sheet1.Range（A1：I& lr）
 .AutoFilter 
 .AutoFilter字段：= 2，Criteria1：==
 .AutoFilter字段：= 3，条件1： ==
 .Offset（1）.SpecialCells （xlCellTypeVisible）.EntireRow.Delete 
 .AutoFilter 
结束
 End Sub 
  

结果应该是一样的，但这种方式应该更快，特别是如果你有很多行。显然，更改列参考以适应您的布局，并随意对其进行错误检查等。

I'm trying to delete an entire row in excel if column B and C are blank for that row. I have this vba code that deletes an entire row if the whole row is blank. How can I only delete the row if B and C have no value?

Thank you

 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

--Update--

The problem is solved. Thanks to izzymo and sous2817

Here is the current code

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

解决方案

As asked for, here is a way to do it without looping:

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

The results should be the same, but this way should be faster, especially if you have a lot of rows. Obviously, change the column reference to suit your layout and feel free to fancy it up w/ some error checking,etc.

这篇关于如果列B和C都为空，Excel VBA将删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！