On 20 Apr 2006 09:52:47 -0700, "sally" wrote:

hi..
Is it possible to have A msgbox that referes to all blank cells in a
sellected range ? something like : "the cell(s) A1,B9,F12,... are
blank."
How can I refer to a probable blank cell range in the msgbox ?
please help..

If it's just a question of referring to that text in a message box
then

MsgBox "the cell(s) A1,B9,F12,... are blank."

will do it.

However if you want to identify the names of specific blank cells,
then you'd need to write the cell addresses to a string variable
Name the Range of cells you're interested in, say A1:F12, as "MyRange"
Then run the following macro.

The limitation is on the length of the string variable you build up,
which I guess is 256 characters. If that's likely to be the case, then
another solution is required, - probably more string variables which
are concatenated for the message box.


Sub IDBlankCell()
Dim stBlankCell As String
Dim rMyCell As Range

For Each rMyCell In Range("MyRange")
If rMyCell = "" Then stBlankCell = stBlankCell &
rMyCell.Address(RowAbsolute:=False, CcolumnAbsolute:=False) & ";"

Next

MsgBox "The cells " & stBlankCell & " are blank."

End Sub


HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________