PDA

View Full Version : Subtotal unique values



vzachin
01-23-2008, 06:14 AM
hi,

i have data in column C. in column B i have the unique values by doing an advanced filter. and finally in column A, i have a countif formula that will give me the count of the unique values from column C.
this was all done manually.

is there a way to write this in vba:
to place the count of the values in corresponding Column A



thanks

Bob Phillips
01-23-2008, 06:39 AM
Sub DoStuff()
Dim LastRowB As Long
Dim LastRowC As Long

With ActiveSheet

LastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("C5").Resize(LastRowC - 4).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("B5"), _
Unique:=True
LastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("A5").Resize(LastRowB - 4).FormulaR1C1 = "=COUNTIF(R5C3:R" & LastRowC & "C3,RC[1])"
End With
End Sub

vzachin
01-23-2008, 07:07 AM
el xld,

very nice.
can you explain how the formula works?
.Range("A5").Resize(LastRowB - 4).FormulaR1C1 = "=COUNTIF(R5C3:R" & LastRowC & "C3,RC[1])"

i know what the formula does but how does it know when to stop in column A?


thanks
zach

Bob Phillips
01-23-2008, 07:35 AM
It is just the formula that you had in the workbook, all I did was VBA it.

It calculates how many rows of data there are in LastRowC and populates that many.