Results 1 to 4 of 4
  1. #1
    Thompyt is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Count if 2 cells in 1 row are not empty

    iRow = ActiveSheet.Range(Not IsEmpty("B"& IPCnt), Not IsEmpty("C"& IPCnt)).Rows(IPCnt).Count



    I have been attempting to sum up all the rows where both columns have something in it. If 1 column is empty, it should not count. I keep getting a 1004 error
    Col B.....Col C
    ...x...............=1
    ...x.........x....=1
    ..............x....=1

    Found a solution:

    Code:
    iRow = WorksheetFunction.CountA(Sheets(WkSht.Name).Range("B" & IPCnt, "C" & IPCnt))     
         If iRow = 2 Then
              DrCnt = DrCnt + 1
         End If
    Last edited by Thompyt; 11-26-2022 at 12:00 AM.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,914
    and that error description is? .......
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,791
    Thanks for posting your solution, and I think it's a good one.
    I've seen that error lots of times lately while trying to help out elsewhere so I'm too familiar with it. However, you really should post both the number and message. Sometimes the same number has different messages, and that's one of them. Even if not, there are thousands of error numbers and no way anyone helping you can remember many of them, nor should they have to look them up to help anyone.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Thompyt is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Will do next time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hiding Empty Cells in Form
    By jaystewart86 in forum Forms
    Replies: 6
    Last Post: 01-20-2018, 08:53 PM
  2. Replies: 7
    Last Post: 07-15-2015, 03:42 PM
  3. Replies: 6
    Last Post: 05-07-2014, 11:15 AM
  4. Replies: 1
    Last Post: 04-15-2014, 02:45 PM
  5. code to delete rows based of empty cells
    By jcbrackett in forum Programming
    Replies: 3
    Last Post: 03-05-2012, 02:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums