count uniques in same column, post in blank cell, repeat until end ofspreadsheet

[S Himmelrich]

A macro that fills the next blank row in same column as represented
below.

Column A
A
A
A
N
N
N
N
[blank cell] result should be "2" -> keep going
A
A
J
L
F
F
F
[blank cell] result should be "4"-> keep going until you get to the
end of the spreadsheet.

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow + 1

If .Cells(i, "A").Value = "" Then

.Cells(i, "A").Formula = "=SUMPRODUCT((A" & StartRow & ":A"
& i - 1 & "<> """")/" & _
"COUNTIF(A" & StartRow & ":A" & i -
1 & ",A" & StartRow & ":A" & i - 1 & "&""""))"
StartRow = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ShaneDevenshire]

Hi,

Here is another solution:

Sub Macro1()
Dim B as Long, S, E, A
B = [A65536].End(xlUp).Row
S = ActiveCell.Address
Do
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
E = ActiveCell.Offset(-1, 0).Address
Loop
A = S & ":" & E
Selection = Evaluate("SUM(1/Countif(" & A & "," & A & "))")
S = ActiveCell.Offset(1, 0).Address
Loop Until Range(E).Row >= B
End Sub