Find blank cells, insert formula that includes cell range above

0
votes

I am trying to find a vba code that will find blank cells in a certain column, and replace these cells with a formula that includes the range of cells above (up until the next blank cell)

for instance, cells A2:A15 are filled in, A16 is blank, I want a code that can find the blank C16, and insert the following code:

=COUNTIF(A2:A13,A2)=COUNTA(A2:A13)

I have about 72,000 rows, and there is not a uniform spacing between the blank cell ranges.

Thank you so much in advance!

This the what the code in have found

Sub Testing()
Dim Rng As Range, r As Range

Set Rng = Range("a2:a" & Range("a" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
Top = Selection.End(xlUp).Value

For Each r In Rng.Areas
    With r
         .Cells(1, 1).Offset(.Rows.Count).Formula = "=countif(" & .Address & ", " & .Top & ") = CountA(" & .Address & ")" 'i got stucked with the code here. where i want to pick the first cell for every range.
    End With
Next
End Sub
2
excelvba
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question. - Pᴇʜ
Does this answer your question? Macro Find Blank cells and Replace - Pᴇʜ
Thanks for you immediate reply Pᴇʜ. i really had a hard time to justify this macro code. i have updated macro code.. where i had trouble. - TCritical

2 Answers

2
votes

Try the following (without VBA at first):

Select your column.
Goto blanks (ctrl+G).
In the formula bar, type your formula.
Press ctrl ENTER.

This should copy the formula into all blank cells you have selected.

Once this works, record it into a VBA macro.

1
votes

Think you just need a coupel of changes to your formula. I replaced Top which I didn't understand and you had an errant "=" instead of a "-".

Sub Testing()

Dim Rng As Range, r As Range

Set Rng = Range("a2:a" & Range("a" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
'Top = Selection.End(xlUp).Value

For Each r In Rng.Areas
    With r
         .Cells(1, 1).Offset(.Rows.Count).Formula = "=countif(" & .Address & ", " & .Cells(1).Address & ") - CountA(" & .Address & ")" 'i got stucked with the code here. where i want to pick the first cell for every range.
    End With
Next

End Sub