excel - 当命名 range1 中的所有值都等于 “N/A” VBA Application.WorksheetFunction 时，希望删除命名 range2

Question

当部分(命名范围“Allowances_Credits_Range”)中的所有值都等于“N/A”时，我们希望删除整个部分的行(命名范围“Remove_Allowances_Credits”)。
例如，当 C161:C170 中的每个值都等于“N/A”时，我们希望删除第 156:171 行。如何使用 application.worksheetFunction count 和 countif 来完成此操作？

If Application.WorksheetFunction.Count(Range("Allowances_Credits_Range")) = Application.WorksheetFunction.Count(Range("Allowances_Credits_Range", "n/a")) Then

    Workbooks(PharmacyPricingGuarantees2).Sheets("Pharmacy Pricing Guarantees").Range("Remove_Allowances_Credits").Delete
End If

我收到错误 1004

If Application.WorksheetFunction.Count(Range("Allowances_Credits_Range")) = Application.WorksheetFunction.Count(Range("Allowances_Credits_Range", "n/a")) Then

    Workbooks(PharmacyPricingGuarantees2).Sheets("Pharmacy Pricing Guarantees").Range("Remove_Allowances_Credits").Delete
End If

删除命名范围“Remove_Allowances_Credits”
我收到错误 1004

Answer 1

不完全确定您要达到的目标，因此我添加了一些不同的方法。

Sub Examples()
    'Might need to change Workbook reference to PharmacyPricingGuarantees2?
    Dim ws As Worksheet: Set ws = Thisworkbook.Sheets("Pharmacy Pricing Guarantees")
    If ws.Range("Allowances_Credits_Range").Cells.Count = WorksheetFunction.CountIf(ws.Range("Allowances_Credits_Range"), "n/a") Then
        ws.Range("Remove_Allowances_Credits").ClearContents  '<-- Clear the values?
        ws.Range("Remove_Allowances_Credits").EntireRow.Delete '<-- Delete the rows?
        ThisWorkbook.Names("Remove_Allowances_Credits").Delete '<-- Delete the named range?
    End If
End Sub