Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count uniques in same column, post in blank cell, repeat until end ofspreadsheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count uniques in same column, post in blank cell, repeat until end of spreadsheet
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) "S Himmelrich" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count uniques in same column, post in blank cell, repeat until end
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 -- Cheers, Shane Devenshire "S Himmelrich" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Uniques in Column, put result in next blank cell andcontinue until last row | Excel Programming | |||
Count Uniques in Column, put result in next blank cell and continu | Excel Programming | |||
Repeat entries in rows 5 & 6 into next blank column | Excel Programming | |||
Count blank cell in a column | Excel Programming | |||
Count Uniques within a list based on value of cell... | Excel Discussion (Misc queries) |