software

hi..
Is it possible to have A msgbox that referes to all blank cells in a
sellected range ? something like : quot;the cell(s) A1,B9,F12,... are
blank.quot;
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 (quot;empty cells: quot; amp; 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 Studentquot;sallyquot; wrote:

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

msgbox quot;The cells quot; amp;
activesheet.usedrange.specialcells(xlcelltypeblank s).address amp; quot; are all
blank--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;sallyquot; gt; wrote in message oups.com...
gt; hi..
gt; Is it possible to have A msgbox that referes to all blank cells in a
gt; sellected range ? something like : quot;the cell(s) A1,B9,F12,... are
gt; blank.quot;
gt; How can I refer to a probable blank cell range in the msgbox ?
gt; please help..
gt;
On 20 Apr 2006 09:52:47 -0700, quot;sallyquot; gt; wrote:

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

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

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

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 quot;MyRangequot;
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(quot;MyRangequot;)
If rMyCell = quot;quot; Then stBlankCell = stBlankCell amp;
rMyCell.Address(RowAbsolute:=False, CcolumnAbsolute:=False) amp; quot;;quot;

Next

MsgBox quot;The cells quot; amp; stBlankCell amp; quot; are blank.quot;

End SubHTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 20 Apr 2006 18:11:59 0100, quot;Bob Phillipsquot;
gt; wrote:

gt;msgbox quot;The cells quot; amp;
gt;activesheet.usedrange.specialcells(xlcelltypeblan ks).address amp; quot; are all
gt;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 againThanks Richard
The great thing about your sub is the quot;absolutequot; parts. They somehow
get rid of $ signs in the msgbox.MsgBox quot;The cells quot; amp; _
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s).Address(False,
False) amp; _
quot; are all blankquot;--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;sallyquot; gt; wrote in message ps.com...
gt; Thanks Richard
gt; The great thing about your sub is the quot;absolutequot; parts. They somehow
gt; get rid of $ signs in the msgbox.
gt;