Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Unique Values Across SpecialCellType Visible Range.
I am trying to sum only the unique values that are visible in a column.
I have an autofilter applied to my worksheet that is hiding dates that are outside of a certain range. In column "Reference No" there are a list of ID values, some of which repeat. If a value repeats, I want to only count the related transaction value, stored in the same row of a different cell, only once. The following code effectively sums unique values across all rows. However, I need it to work only within the range of visible cells. Is this possible using the autofilter, or do I need to add a second argument to my "IF" statement regarding the date range (would prefer not to, since this would inevitably be slower). code: -------------------------------------------------------------------------------- Set rng2 = .Range(.Cells(1, 1), .Cells(2 ^ 16, rng.Count).SpecialCells(xlCellTypeVisible).End(xlU p)) For i = 1 To rng.Count If cName(i) = "Reference No" Then Set rng2 = .Range(.Cells(1, i), .Cells(2 ^ 16, i).End(xlUp)) For j = 2 To rng2.Count istrue = True If .Cells(j, i).Value < .Cells(j + 1, i).Value _ And .Cells(j, tDate).Value LowYear Then totalvalue = totalvalue + .Cells(j, i + 1) istrue = False End If Next j End If Next i -------------------------------------------------------------------------------- |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
count unique values in a filtered range | Excel Worksheet Functions | |||
Unique values from date range | Excel Discussion (Misc queries) | |||
How do I get the unique values from a range? | Excel Worksheet Functions | |||
Display unique values in a range. | Excel Programming |