Solved: Filter unique values and count their frequency

[danlu]

Hi,

I have a code which is intended to first filter out all unique values from a sheet and paste these values into sheet2.
Then I want to determine the size of this range where the unique values where pasted. For every value within this range it should step through each value and count how many times each such value occurs on the original sheet (that is, the sheet from where the unique values were extracted from)

Though I have not managed to resize the range, called malomrade, properly to make it only contain cells which contains a unique value, and hence make the For Each c mechanism step through only the cells which actually contains values and make a count for every such cell. Any ideas how this is done? I have below marked it something?? to indicate where I have a hard time coming up with how to VBA code it. Of course maybe it can also be done in an alternate way, all ideas are welcome.

[VBA]Sub test()
Dim malomrade As Range
Dim c As Range

Set malomrade = Sheets(2).Range("A1:A100")

Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
malomrade, Unique:=True


Set malomrade = malomrade.Resize(something??.Rows.Count)

For Each c In malomrade

c.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets(1).Range("A:A"), c.Value)

Next c

End Sub[/VBA]


I want it to look as it does in the attached file on sheet2. (Though there I have manually erased the values in column B for values from row 19 and below since it goes through down to row 100 because malomrade is as described above no resized properly).
On sheet1 is the intitial values from where the unique values are extracted and from where the count should be made in the last step of the code.

[david000]

You could try a, Pivot Table, they automatically count text entries.
I just copied the entire row and the pivot table wizard did the rest.

[danlu]

Hi,

Yeah by using the data in column A as the element both for the row field and the data field it seems to display all unique values in the row field and count the number of occurrences of every such unique value in the data field. Thanks for your help.

To improve my knowledge of the resize property, is there some way to determine which cells that actually contain a value and resize the range to include only cells meeting that criteria?

[Bob Phillips]

Rows.Count and Columns.Count of the 'something' give you that.

[danlu]

Ok thanks, but what I have a hard time coding is how to find out the size of the range where all the unique values were pasted. This range will of course differ from time to time depending on how many unique values there are in the sheet at hand. For ex if there would be 25 unique values then I want to first find out how big the range should be, in this case 25 cells, and then do the resize as you suggested.
So in short, some way to determine how big the range is at each current occassion and then do the resize to fit that occassion's number of unique values.

[Bob Phillips]

[vba]

first_cell_of_target.CurrentRegion.Rows.Count
[/vba]

[mdmackillop]

I put this together a while ago. Any use?

[danlu]

Thanks, adding your extra line of code did the trick. Great!

[VBA]
Sub test()
Dim malomrade As Range
Dim c As Range

Set malomrade = Sheets(2).Range("A1:A100")

Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
malomrade, Unique:=True


Set malomrade = malomrade.Resize(Sheets(2).Range("A1").CurrentRegion.Rows.Count)

For Each c In malomrade

c.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets(1).Range("A:A"), c.Value)

Next c

End Sub
[/VBA]

Mdmackillop, thanks for the page with more info you provided!