check if any cell in a dynamic range is empty

I need to make sure all the inputs are entered before running a macro. In order to do that, I would like to make sure none of the cells in the range (which is dynamic and I know the range) is empty. How to do that?

Thanks in advance, Anil

Here's what I would do. Assuming your dynamic range is named "named"

=IF(ROWS(Named)>COUNTA(Named),"Blanks","No Blanks")

It counts the number of rows against the number of entries. Does that work for you?

ChemistB

Hello akabraha,

Here is another way. With this method you can identify the cells that are blank (empty). It will display a message box listing the cells that are blank.

Sub CheckForBlanks(Rng As Range)

  Dim Blanks As Range
  
    On Error Resume Next
      Set Blanks = Rng.SpecialCells(xlCellTypeBlanks)
    
    If Not Blanks Is Nothing Then
      For Each Addx In Split(Blanks.Address, ",")
        Msg = Msg & Addx & vbLf
      Next Addx
        If Msg <> "" Then
          MsgBox "The Following cells are empty:" & vbLf & Msg
        End If
    End If
    
End Sub

Example

Sub Test()
 'Will display a list of empty cells in the range, if there are any
  CheckForBlanks Range("A2:F465")
  
End Sub

Sincerely,
Leith Ross

Thank you ChemistB and Leith Ross (you are the Excel Guru). CountA worked for me.

Thanks again, Anil