Msgbox experts please reply..

S

sally

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

Gary''s Student

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

Bob Phillips

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Richard Buttrey

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..
Click to expand...

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
__________________________
 
R

Richard Buttrey

msgbox "The cells " &
activesheet.usedrange.specialcells(xlcelltypeblanks).address & " are all
blank
Click to expand...

Brilliant Bob.

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

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
S

sally

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

sally

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

Bob Phillips

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top