Share via

How to check that all cells in range are not empty using VBA?

Anonymous
2024-02-22T20:50:34+00:00

Hi,

I have a spreadsheet similar to what is shown below, and I need to use VBA to ensure that none of the data entry cells (blue cells on form) are empty when I go to print the form. I know I can check each cell using If Range(Cell)="" and Range(Cell2)=""... but I would like to know if there is a better way: Maybe check the whole group of cells by naming the entire group of blue cells; Checking entire ranges; Looping through all cell options; Or any another better option.

Thanks, SP

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2024-02-22T21:40:52+00:00

    You could use

    If Application.CountA(Range("A2:H2,B3:I4,B6:V7,B9:V9,B11:V11,B13:V13,B15:V15")) = 0 Then
    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2024-02-23T14:30:51+00:00

    Oh wait - this will only work if ALL cells are empty. Try

    If Application.CountA(Range("I8,R8,AB8,F30:M30,F111,Q111")) < Range("I8,R8,AB8,F30:M30,F111,Q111").Count Then
    0 comments
  2. Anonymous
    2024-02-23T14:51:33+00:00

    Thanks again. I just modified your original formula to add <> # of cells, and it's working great.

    Really appreciate your help!!

    0 comments