Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RE: Counting number of cells (Excel XP SR1)

    Which is best command for counting the number of:

    1) Total number of cells within a range in a column
    2) The total number of cells that have entries (i.e. no blanks) within a given range in a column?

    Many thanks.

    Farmer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: RE: Counting number of cells (Excel XP SR1)

    Say that you have a range variable oRange, and you're interested in column D.

    1) Intersect(oRange, Range("D")).Count is the number of cells in oRange within column D.
    2) Application.WorksheetFunction.CountA(Intersect(oRa nge, Range("D"))) is the number of non-blank cells in oRange within column D.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Counting number of cells (Excel XP SR1)

    Thanks for that. Do I need to Dim the range variable? Is it a range name or eg B3:B10 declared? Also ,can I put e value somewhere like
    x = Intersect(oRange, Range("D")).Count

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: RE: Counting number of cells (Excel XP SR1)

    You can use a literal range instead of oRange, e.g. Range("B310"). If you use a variable, and if you require variable declaration (you should!), you must declare (dim) it before using it.
    Once you have declared a variable, you can set it in many different ways, e.g.
    Set oRange = Range("B310")
    or
    Set oRange = Range("MyName")
    or
    Set oRange = Range("B3").CurrentRegion
    or
    Set oRange = Range(Range("B3"), Range("D65536").End(xlUp))
    etc.

    You can declare a variable of type Long and assign the result of the count to it:

    Dim x As Long
    x = Intersect(oRange, Range("D")).Count

    then use x somewhere else in your code.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Counting number of cells (Excel XP SR1)

    Many thanks, much appreciated.

    F

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •