Count Records Problem

[bogdan]

I have a couple of excel worksheets all in the same formats. And I have to extract how many values of each kind i have. I apply a filter and after that I try to get the number of records. But I can't find a reliable method. Any ideas could help.

[Andrew-R]

Could you post an example workbook showing us what you're trying to do?

[bogdan]

Can't upload from where I am now.
But for example:in column C I have various types of colors. And I try to find out how many of a specific color I have. If I search one that has no appereance will still return 1.

          ActiveSheet.Range("A1:C1").AutoFilter Field:=3, Criteria1:= _
                "=white", Operator:=xlOr
            Set rng = ActiveSheet.Range("A2:A" & ActiveSheet.Range("A65536").End(xlUp).Row)
            white= rng.SpecialCells(xlCellTypeVisible).Count

I would expect an error or smth.

[Andrew-R]

Can't you just use:

white=WorksheetFunction.CountIf(ActiveSheet.Range("A:A"),"white")

[bogdan]

It would work very smooth but for color "blue" for example I need to copy the results in another worksheet so this is why I choosed the filtering way.

[Andrew-R]

How many rows of data will you have to copy at once? Personally I'd go with a .Find within a loop to match and copy each row - I just plain don't like using filters.

[bogdan]

Well..that's the problem. I have smth like 25 worksheets with 1000 to 3000 lines. And both the number of worksheets and lines can increase in time.

[snb]

you can use advancedfilter.
Check the number of unique items first:
After that you can use advancedfilter to split the data in separate sheets

sub snb()
  sheets(1).columns(3).advancedfilter xlfiltercopy,,sheets(1).cells(1,20),true

  sn=sheets(1).columns(20).specialcells(2)
  sheets(1).cells(1,20).offset(2).reszie(ubound(sn).clearcontents

  for j=2 to ubound(sn)
    sheets(1).cells(2,20)=sn(j,1)
    sheets(1).cells(1).currentregion.advancedfilter xlfiltercopy,cheets(1).cells(1,20).currentregion,sheets(2).cells(1)
  next
End Sub

[Andrew-R]

Are you feeling OK, snb? Not only is your code *way* more than 1 line, but you've also added a whole new 'reszie' method for the range object

Edited to add: Oh, and a 'cheets' object. Who are you, and where's the real snb?