Hi Howard,

Am Fri, 30 Jun 2017 14:51:04 -0700 (PDT) schrieb L. Howard:

First I want to change/adjust is to remove the last , (comma) in the MsgBox listing of cells that have a "" or (Select One) in them.

This code line adds the , (commas)
cc = cc & " " + c.Address(False, False) & ", "

and the list looks like this example in the msgbox B5, C6, G8,

Want it to look like this B5, C6, G8
(last comma is gone)

Second, instead of selecting the whole OneRng, select only the cells that are listed in the msgbox - the cc values

try:

Sub NextTab_1()
Dim bRow As Long, LRow As Long, cCnt As Long
Dim OneRng As Range, c As Range
Dim cc As String

With ActiveWorkbook.Worksheets("4. Property Information")
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set OneRng = .Range("B4:S" & LRow).SpecialCells(xlCellTypeVisible)

For Each c In OneRng
If c = "" Or c = "(Select One)" Then
cc = cc & ", " + c.Address(False, False)
cCnt = cCnt + 1
End If
Next

If cCnt 0 Then
.Range(Mid(cc, 2)).Select
MsgBox "There are " & cCnt & _
" cells with ""Blank"" or ""(Select One)"" in these cell address'." _
& vbCr & vbCr & Mid(cc, 2)
Else
MsgBox "All data point are positive."
Sheets("(Lists)").Visible = True
Sheets("(Lists)").Activate
End If

End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016