Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count data in column by exlude some records (Excel 2002)

    Hi,
    how can i count a column A to exclude some records.
    i need the result be 3. exclude the count for off hours.

    below command can allow me to count all the column data, how can i add to exclude some data in a column.
    lngCountCalender = wshA.Cells(65536, 1).End(xlUp).Row

    Column A
    Off Hours
    Normal Working Hours
    Normal Working Hours
    Normal Working Hours

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

    Re: count data in column by exlude some records (Excel 2002)

    You can do this in a worksheet formula:

    =COUNTA(A:A)-COUNTIF(A:A,"Off Hours")

    If you need this in VBA code, you can either write a loop to perform the count, or translate the above formula into code:

    <code>lngCountCalender = Application.WorksheetFunction.CountA([A:A]) - _
    Application.WorksheetFunction.CountIf([A:A], "Off Hours")</code>

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count data in column by exlude some records (Excel 2002)

    hi yeap it works! thanks....

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count data in column by exlude some records (Excel 2002)

    Just to avoid confusion by others reading this thread, your original VBA statement:

    <pre> lngCountCalender = wshA.Cells(65536, 1).End(xlUp).Row
    </pre>


    does not count anything. It gives the row number of the last used cell in column A of the worksheet. If there are no empty cells in the column, then this number happens to be the same as the count of the cells in column A that contain data. However, if there are empty cells, then it is just the row number of the last cell with data.
    Legare Coleman

Posting Permissions

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