How to delete entire rows of data containing two or less nonzero values in Excel VBA?

Question

I am fairly new to coding utilizing the VBA language, and I am stuck trying to automate a mass spectrometry data collection process. Part of the process involves evaluating a row of Excel data, and if it has two or less nonzero values, the concentration of protein is considered insignificant to the overall results of the sample, and the entire row is therefore deleted.

This part of my code copies data from one spreadsheet to another for manipulation and deletes all of the blank rows (with no values whatsoever) so how would I go about iterating through each row and removing the insignificant ones?

Set wb = Workbooks.Open("C:\Users\X241066\Downloads\PGroupTest.xlsm")
myFile = "C:\Users\X241066\Desktop\Pgroup\proteinGroups.xls"
Workbooks.Open myFile
Sheets("proteinGroups").Range("IY1:KU5463").Copy
wb.Activate
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Removes all blank rows
Dim r As Range, rows As Long, j As Long
Set r = ActiveSheet.Range("A1:AZ5463")
rows = r.rows.Count
For j = rows To 1 Step (-1)
If WorksheetFunction.CountA(r.rows(j)) = 0 Then r.rows(j).Delete
Next

Answer 1

I believe you are looking for:

If WorksheetFunction.CountIF(r.rows(j), 0) > 2 Then r.rows(j).Delete

So we check in the range of the row, if it has more than 2 zero values. The second argument is used to compare against.