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..

I am not an expert, but look at this small macro:

Sub Macro1()
Dim r As Range
Dim rr As Range
For Each r In Selection
If IsEmpty(r.Value) Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
MsgBox ("empty cells: " & rr.Address)
End Sub

It looks thru Selection for empty cells and builds a range of them.
It then displays the address associated with that range.

--
Gary's Student


"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..

msgbox "The cells " &
activesheet.usedrange.specialcells(xlcelltypeblank s).address & " are all
blank


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"sally" wrote in message
oups.com...
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..

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
__________________________

On Thu, 20 Apr 2006 18:11:59 +0100, "Bob Phillips"
wrote:

msgbox "The cells " &
activesheet.usedrange.specialcells(xlcelltypeblan ks).address & " are all
blank

Brilliant Bob.

Why can I never see the simple answer, and overcomplicate things.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thanks Gary's Student
You are an expert..

Thanks a lot Bob
It's amazing how some combinations can save us time and effort
Thanks again

Thanks Richard
The great thing about your sub is the "absolute" parts. They somehow
get rid of $ signs in the msgbox.

MsgBox "The cells " & _
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s).Address(False,
False) & _
" are all blank"


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"sally" wrote in message
ps.com...
Thanks Richard
The great thing about your sub is the "absolute" parts. They somehow
get rid of $ signs in the msgbox.