VBA How to count duplicate blank cell

Question

I want to count how many duplicate blank cells does each person has.

Here is the example: (E is the number of duplicate blank cells that the person has)

Beginning:

A.        B.         C.       D.


Tom                  No       9/7/2021
Tom                  No      10/7/2021
Tom                  No      13/7/2021
Amy      Yes         No      15/7/2021
Kristy   Yes                 17/7/2021
Kristy                       18/7/2021 


Ken                           4/7/2021
Ken      Yes                  5/7/2021

Expected Result:

A.        B.         C.       D.              E.


Tom                  No       9/7/2021        3
Tom                  No      10/7/2021        3
Tom                  No      13/7/2021        3
Amy      Yes         No      15/7/2021        0
Kristy   Yes                 17/7/2021        2  
Kristy                       18/7/2021        2 


Ken                           4/7/2021        2
Ken      Yes                  5/7/2021        2

Welcome to Stack Overflow! However, this is not a coding service. Therefore, if you want help, [show your attempts](https://idownvotedbecau.se/noattempt/) and explain what did or did not work. Don't expect us to do your work for you. — Victor Colomb, Jul 28 '21 at 08:43
@Bharat. Blank in Column B and Column C. Column E is the result — Dicer, Jul 28 '21 at 08:45
Sorry. I just edited my question. Now, the question should be 100% clear and correct. — Dicer, Jul 28 '21 at 08:50
_Now, the question should be 100% clear_ well, no... is it the count of rows where B or C is blank? Is it the max of the counts of blanks in each of B and C? Is it something else? — chris neilsen, Jul 29 '21 at 07:38
Sorry. I answer you wrongly. Kristy's count is still 2 because one of them is not an empty cell. — Dicer, Jul 29 '21 at 08:52

Victor Colomb · Accepted Answer · 2021-07-29T08:14:32.997

1

Sub CountBlankDuplicates()
    Dim oNameCell As Range
    Dim oCell As Range
    Dim count As Long
    
    For Each oNameCell In Range("A1", Range("A1").End(xlDown))
        count = 0
        For Each oCell In Range("A1", Range("A1").End(xlDown))
            If oNameCell = oCell And (oCell.Offset(, 1) = "" Or oCell.Offset(, 2) = "") Then
                'If names correspond and column B is not blank
                count = count + 1
            End If
        Next oCell
        
        oNameCell.Offset(, 4) = count 'Offset(,4) from column A is column E
    Next oNameCell
    
    Set oNameCell = Nothing
    Set oCell = Nothing
End Sub

edited Jul 29 '21 at 08:14

answered Jul 28 '21 at 09:07

Victor Colomb

338
2
16

I think your code is logically correct, but I want to store the value in Column E. – Dicer Jul 28 '21 at 10:03
`oNameCell.Offset(, 4) = count` does exactly that. If you go 4 cells to the right of column A, you're in column E. I could have written `Range("E" & oNameCell.Row) = count` or `Cells(oNameCell.Row, 5) = count` it would have done the same. (See [offset documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.offset).) – Victor Colomb Jul 28 '21 at 10:36
Sorry, but where is column C? – Dicer Jul 29 '21 at 03:46
And how do you count column C? because I don't see any information about Column C – Dicer Jul 29 '21 at 07:16
Sorry. I. think it is my problem. I just correct my question. Now, the question should be correct. You also need to count if column B and column C are blank. – Dicer Jul 29 '21 at 07:22
I edited my answer. I only had to slightly change the condition for which `count` is incremented. – Victor Colomb Jul 29 '21 at 07:24
Sorry. Your code this part **Next oCell** has compile error: Next without For – Dicer Jul 29 '21 at 08:09
Yes. It can solve the problem. But if the data is not continuous, your code doesn't work. I edit the question again. Your code is not applicable in the situation. – Dicer Jul 29 '21 at 09:15
Why don't you do some research on your own? Look for example at https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba to figure out the last row. – FunThomas Jul 29 '21 at 09:22
@Fun Thomas. I think you misunderstand the question. Victor Colomb's code is good. His code can solve my question. I just want to point out that his code doesn't work if the excel data is discrete (eg. A row is not empty. B row is empty. C row is not empty. ) – Dicer Jul 29 '21 at 10:28
As long as column A is not empty it will work since I use `.End(xlDown)` on column A. You can always use `Range("A:A")` to loop through the entire A column. However, I agree with @funthomas, it's time you tried on your own. I already answered your question multiple times throughout your edits. – Victor Colomb Jul 29 '21 at 10:33

score 0 · Answer 2 · answered Jul 28 '21 at 08:50

0

You will need two columns to calculated and no VBA.

Write in E9: =IF(B9="";A9;"")

Write in F9: =CountIF(E:E;A9)

answered Jul 28 '21 at 08:50

Samuel

2,923
1
4
19

I need to use VBA. Excel formula is not allowed in this case – Dicer Jul 28 '21 at 08:52
Which instance is it that forbids to use formulas? It this a homework that shall prove your VBA knowledge? – FunThomas Jul 28 '21 at 08:58
My company wants me to use VBA to automate some manual processes, so Excel formula is not allowed. – Dicer Jul 28 '21 at 09:00

score 0 · Answer 3 · answered Jul 29 '21 at 09:12

Sub Answer()
Dim Sht as worksheet
Set Sht = Activeworkbook.Sheets("Sheet1") 'edit this to refer  to your sheet
With Sht
For a = 2 to .UsedRange.Rows.Count
    .Cells(a, 5) = WorksheetFunction.COUNTIFS(.Range("A:A"), .Cells(a, 1), .Range("B:B"), "", .Range("C:C"), "")
Next a

That will return the count per person where both B and C are blank, which is what you asked for on the duplicate question you asked about this - if it's a different requirement the COUNTIFS( section will need tweaking.

score -1 · Answer 4 · answered Jul 28 '21 at 08:50

-1

You can use the COUNTBLANK function

4 - COUNTBLANK(A1:A4)

answered Jul 28 '21 at 08:50

rzuberi

11
1

This does not answer the question. Column A is never blank according to OP and you don't take column B into account. – Victor Colomb Jul 28 '21 at 08:55

Samuel · Answer 5 · 2021-07-29T14:49:41.627

Record a macro while entering the formulas and paste them over with their values. If you optimze your code a bit, it could look like that. Further optimization would remove most of the select instruction, by using the set instruction. This is of course not the best way of solving a problem; however, it this is a general route, if you need a VBA script and already know how to solve the issue with an Excel formula.

    dim Rng
    Rng = Range("A1").End(xlDown).Row
    If Rng < 50 Then Rng = 50
   
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2").FormulaR1C1 = "=IF(RC[-3]="""",RC[-4],"""")"
    Range("F2").FormulaR1C1 = "=IF(RC[-5]<>"""",COUNTIF(C[-1],RC[-5]),"""")"
    Range("E2:F2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("E2:F" & Rng), Type:=xlFillDefault
    Columns("F:F").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("E3").Select
    Selection.Copy

[How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/12425723) — Victor Colomb, Jul 28 '21 at 09:41
Thanky you @Victor for you good Comment. I wanted to give the asker DD_ a route, how to solve the task with recording a macro and doing some easy modifications. — Samuel, Jul 29 '21 at 15:04

VBA How to count duplicate blank cell

5 Answers5