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

So that only the culprit cells are highlighted.

OneRng.Activate (just the cc cells selected here, not the whole range)
MsgBox "There are " & cCnt & _
" cells with ""Blank"" or ""(Select One)"" in these cell address'." & vbCr & vbCr & cc


Thanks,
Howard


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

With ActiveWorkbook.Worksheets("4. Property Information")


Set OneRng = Range("B4:S" & Range("B4").End(xlDown).Row).SpecialCells(xlCellTy peVisible)

For Each c In OneRng
If c = "" Or c = "(Select One)" Then

cCnt = cCnt + 1
cc = cc & " " + c.Address(False, False) & ", "

End If

Next
If cCnt 0 Then

OneRng.Activate
MsgBox "There are " & cCnt & _
" cells with ""Blank"" or ""(Select One)"" in these cell address'." & vbCr & vbCr & cc

Else

MsgBox "All data point are positive."
Sheets("(Lists)").Visible = True
Sheets("(Lists)").Activate

End If

End With


End Sub

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

On Friday, June 30, 2017 at 3:43:57 PM UTC-7, Claus Busch wrote:
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.
--

Hi Claus,

That is excellent.

Had a puzzle at first because this line <.Range(Mid(cc, 2)).Select was throwing an error. I hovered over "cc" and it was showing many many empty cells outside of OneRng...???

I had to re-instate my original OneRng definition of:

Range("B4:S" & Range("B4").End(xlDown).Row).SpecialCells(xlCellTy peVisible)

because OneRng can only go down as far as row 18. From about row 22 and on down there is other data.

All is wonderful now!

Thanks for the fix, appreciate it.

Howard

Hi Howard,

Am Fri, 30 Jun 2017 16:34:21 -0700 (PDT) schrieb L. Howard:

Had a puzzle at first because this line <.Range(Mid(cc, 2)).Select was throwing an error. I hovered over "cc" and it was showing many many empty cells outside of OneRng...???

I had to re-instate my original OneRng definition of:

Range("B4:S" & Range("B4").End(xlDown).Row).SpecialCells(xlCellTy peVisible)

xlDown is not reliable if there is an empty cell in column B. Then the
range goes only to the cell above the empty cell.

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(20, "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, 3)).Select
MsgBox "There are " & cCnt & _
" cells with ""Blank"" or ""(Select One)"" in these cell
address'." _
& vbCr & vbCr & Mid(cc, 3)
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