excel - vba ：有什么方法可以删除行数吗？应用后删除重复项（单个/多个）

Question

我的疑问是：在 VBA 编码中，有没有可能知道，运行以下过程后有多少行受到影响

Sub RemoveDuplicatesWithNoHeaders()
Range("A1:D35").RemoveDuplicates Columns:=Array(1), Header:=xlNo ' for mutiple columns Array(1, 2, 3, 4)
End Sub

运行此程序后，我们能否获得删除了多少行

标签： excelvba

Answer 1

更新：好的，当我尝试将之前的答案放入函数时，它不起作用，所以很明显我的测试有问题......

这是一个应该实际工作的替代方案（通过计算重复删除前后范围内的空白单元格的数量）

Sub TestDupsCount()

    Debug.Print RemoveAndCountDuplicates(Range("A1").CurrentRegion, _
                                         Array(1, 2, 3, 4), xlNo)

End Sub

Function RemoveAndCountDuplicates(rng As Range, ArrCols, _
                                  haveHeaders As XlYesNoGuess)
    Dim oldBlank As Long, newBlank As Long, rv
    rv = 0
    oldBlank = NumEmptyRows(rng)
    'https://stackoverflow.com/question/39104653/vba-remove-duplicates-fails-when-columns-array-is-passed-using-a-variable
    rng.RemoveDuplicates Columns:=(ArrCols), Header:=haveHeaders
    newBlank = NumEmptyRows(rng)
    RemoveAndCountDuplicates = newBlank - oldBlank
End Function
Function NumEmptyRows(rng As Range) As Long
    Dim rw As Range, i As Long, cols As Long
    cols = rng.Columns.Count
    For Each rw In rng.Rows
        If Application.CountBlank(rw) = cols Then i = i + 1
    Next rw
    NumEmptyRows = i
End Function