Search code examples
excelvbacountdelete-rowcountif

Looking to delete named range2 when all values in named range1 are equal to “N/A” VBA Application.WorksheetFunction

We are looking to delete an entire section of rows (named range “Remove_Allowances_Credits”) when all values in the section (named range "Allowances_Credits_Range") are equal to “N/A”.
For example, we are looking to delete rows 156:171 when each value in C161:C170 equal “N/A”. How do I use application.worksheetFunction count and countif to accomplish this?

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

I am getting an Error 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

To delete the named range "Remove_Allowances_Credits" I am getting an error 1004

Solution

  • Try the following:

    If Application.WorksheetFunction.COUNTA(Range("Allowances_Credits_Range")) = 
Application.WorksheetFunction.COUNTIF(Range("Allowances_Credits_Range"), "#N/A")) Then

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

End If

    Firstly, you want to use COUNTA instead of COUNT for the first part. COUNT only counts numerical values. COUNTA counts all non-empty values (including #N/A).

    Secondly, you want to use the COUNTIF function to count values = #N/A.