Sub CCC()
'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If
End Sub

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
Thanks so much to both of you!

For some reason, I wasn't able to get Doug's to work, but I was probably
putting it in the wrong part of my code

Tom - I plugged some of your code into my code and it does work -- except
for formatting. I am getting a result returned as follows:

The blank cells a
D13, D14
D15, D16, D17
D18, D19, D20

Note the D13 and D14 -- there should be three in that row; not 2, and also
the D13 is out of line with the D15 and D18.

Here is my code. What am I doing wrong?

'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & ", " & rCell.Address(0, 0)
End If
Next rCell

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
sStr = Mid(sStr, 3)
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If

--
Sandy


"Sandy" wrote:

Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a
vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy