Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Count Blank/Empty Cells... (xp-pro - office 2003)

    hi experts...

    i have the following statement to check if a range of cells is "empty"...

    now, if i have a new workbook just opened, are the cells "empty" or not, or is my statement wrong because i get strange results, although i have not entered any "blanks-spaces"...

    Selection.Cells.SpecialCells(xlCellTypeBlanks).Cou nt

    thank you...

    best,
    wolfgang

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

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    Apparently, only blank cells within the used range of the worksheet are included in SpecialCells(xlCellTypeBlanks). In a new workbook, the used range is empty, so SpecialCells(xlCellTypeBlanks) is empty too.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    hi hans...

    thanks and that's what i thought as well...

    would you please have a look at the attached wb and run my macro, alt-f8 and be amazed by its results...

    best,
    wolfgang

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

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    Selection.Cells.Count returns the count of ALL cells, whether they're in the used range of the worksheet or not. The selection in your worksheet consists of 6 columns x 22 rows = 132 cells.

    Selection.SpecialCells(xlCellTypeBlanks) returns the count of blank cells within the used range of the worksheet. The used range is A1:C9, containing 3 x 9 = 27 cells. Only C9 is filled, so the number of blank cells is 27 - 1 = 26.

    Because of the way xlCellTypeBlanks works, subtracting leer = 26 from voll = 132 is not valid - it's like comparing apples with pears.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    bingo hans....

    *it's like comparing apples with pears.*

    it's kinda fun...now i have "appears" ...

    have a nice weekend...

    best,
    wolfgang

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    Why not use the line:
    <pre>leer = Application.WorksheetFunction.CountBlank(Selection )</pre>


    Steve

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Re: Count Blank/Empty Cells... (xp-pro - office 2003)

    good morning steve....

    thank you very much for the hint....

    "can't see the forrest for the trees" or something like that...ah well, i'm becoming of age....

    best,
    wolfgang

Posting Permissions

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