如何在Excel VBA中没有循环的情况下删除多行

Frequently we are asked how to delete rows based on criteria in one or more columns, and can we use a SpecialCells trick for this?

我们经常被问到如何根据一列或多列中的条件删除行,我们可以使用SpecialCells技巧吗?

3 个解决方案

#1

First, let me say categorically that there is nothing wrong with loops - they certainly have their place!

首先,让我断言循环没有任何问题 - 他们肯定有自己的位置!

Recently we were presented with the below situation:

最近我们得到了以下情况:

400000  |  Smith, John| 2.4   | 5.66|   =C1+D1
400001  |  Q, Suzy    | 4.6   | 5.47|   =C2+D2
400002  |  Schmoe, Joe| 3.8   | 0.14|   =C3+D3
Blank   |             |       |     |   #VALUE
Blank   |             |       |     |   #VALUE

The OP wanted to delete rows where Column A is blank, but there is a value in Column E.

OP希望删除列A为空的行,但列E中有一个值。

I suggest that this is an example where we could make use of SpecialCells and a temporary Error Column to identify the rows to be deleted.

我建议这是一个例子,我们可以利用SpecialCells和一个临时错误列来识别要删除的行。

Consider that you might add a column H to try and identify those rows; in that row you could use a formula like below:

考虑您可以添加一列H来尝试识别这些行;在该行中,您可以使用如下公式:

=IF(AND(A:A="",E:E<>""),"DELETE THIS ROW","LEAVE THIS ROW")

now, it is possible get that formula to put an error in the rows where I test returns True. The reason we would do this is a feature of Excel called SpecialCells.

现在,有可能得到该公式在我测试返回True的行中放入错误。我们这样做的原因是Excel的一个名为SpecialCells的功能。

In Excel select any empty cell, and in the formula bar type

在Excel中选择任何空单元格,并在公式栏中键入

=NA()

Next, hit F5 or CTRL+G (Go to... on the Edit menu) then click the Special button to show the SpecialCells dialog.

接下来,按F5或CTRL + G(转到编辑菜单上的...),然后单击特殊按钮以显示SpecialCells对话框。

In that dialog, click the radio next to 'Formulas' and underneath, clear the checkboxes so that only Errors is selected. Now click OK

在该对话框中,单击“公式”旁边的单选按钮,然后清除复选框,以便仅选择“错误”。现在单击确定

Excel should have selected all the cells in the worksheet with an Error (#N/A) in them.

Excel应该已选择工作表中的所有单元格,其中包含错误(#N / A)。

The code below takes advantage of this trick by creating a formula in column H that will put an #N/A in all the rows you want to delete, then calling SpecialCells to find the rows, and clear (delete) them...

下面的代码通过在H列中创建一个公式来利用这个技巧,该公式将在您要删除的所有行中放置#N / A,然后调用SpecialCells来查找行,并清除(删除)它们......

    Sub clearCells()
    '
    Dim sFormula As String
    '
    ' this formula put's an error if the test returns true, 
    ' so we can use SpecialCells function to highlight the
    ' rows to be deleted!

Create a formula that will return #NA when the formula returns TRUE

创建一个公式,当公式返回TRUE时将返回#NA

sFormula = "=IF(AND(A:A="""",E:E<>""""),NA(),"""")"

Put that formula in Column H, to find the rows that are to be deleted...

将该公式放在H列中,以查找要删除的行...

Range("H5:H" & Range("E65536").End(xlUp).Row).Formula = sFormula

Now use SpecialCells to highlight the rows to be deleted:

现在使用SpecialCells突出显示要删除的行:

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.select

This line of code would highlight just Column A by using OFFSET in case instead of deleting the entire row, you wanted to put some text in, or clear it

这行代码将通过使用OFFSET突出显示A列,而不是删除整行,您想要放入一些文本,或清除它

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -7).select

and the below line of code will delete thhe entire row because we can :)

以下代码行将删除整行,因为我们可以:)

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlup

' clean up the formula
Range("H5:H" & Range("E65536").End(xlUp).Row).Clear
'
End Sub

BTW, it's also possible WITH A LOOP if you really want one :)

顺便说一句,如果你真的想要一个,它也可以用一个循环:)

One more thing, before Excel 2010 there was a limit of 8192 rows (I think because this feature went all the way back to 8-bit versions of Excel maybe)

还有一件事,在Excel 2010之前有8192行的限制(我想因为这个功能可能会一直回到8位版本的Excel)

The VBA legend Ron de Bruin (on whose website I first picked up this technique, among others) has something to say about this

VBA传奇人物Ron de Bruin(在我的网站上我首先采用了这种技术,等等)对此有所说明

Philip

#2

Alternatively you can use auto filters:

或者,您可以使用自动过滤器:

Sub clearCells()
'
' Example code for * post
'http://*.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba
'
Dim rngTable As Range
Dim ws As Worksheet
Dim StartCell As Range

Const ColumntoFilter1 As Integer = 1
Const FilterCriteria1 As String = "="
Const ColumntoFilter2 As Integer = 5
Const FilterCriteria2 As String = "<>"

Set ws = ActiveSheet
'Set the starting position (Top-left most position) of your data range
Set StartCell = ws.Range("A1")

'Turn off autofilter in case it's already active
ws.AutoFilterMode = False
'Define data table
Set rngTable = StartCell.CurrentRegion
'Filter and delete data
With rngTable
    .AutoFilter Field:=ColumntoFilter1, Criteria1:=FilterCriteria1
    .AutoFilter Field:=ColumntoFilter2, Criteria1:=FilterCriteria2
    .Offset(1, 0).EntireRow.Delete
End With

'Turn filters off again
ws.AutoFilterMode = False

Set rngTable = Nothing
Set StartCell = Nothing
Set ws = Nothing
End Sub

#3

This will delete row numbers m to n passed through the function

这将删除通过该函数传递的行号m到n

Sub Delete_Multiple_Rows (m as Integer, n as Integer) 

    Rows(m & ":" & n).EntireRow.Delete 

End Sub

#1