VBA: Show Message "Empty Data" Into A Range

[Susanto3311]

hi all..

i have a sheet that contains data,formula, merge cell and coloring background & no coloring
i want to check cells only that empty data / empty cell into no coloring cell, with step like this
1. click the vba code;
2. select range that to running, ok
3. show message/info "No Empty " or "Cell C2 is Empty or Cells C2,C3 are Empty, etc......."

the code running well in any name sheets.
here attachment file.
anyone help, greatly appreciated..

.susant

[HansV]

See the attached workbook.

sample vba.xlsm

[Susanto3311]

hi Hans, thank you so much. Working well.

[Susanto3311]

hi hans, i think i need modify your code

Code: Select all

Sub test()
    Dim rng As Range
    Dim cel As Range
    Dim msg As String
    On Error Resume Next
    Set rng = Application.InputBox(Prompt:="Select a range", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then
        Beep
        Exit Sub
    End If
    For Each cel In rng
        If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone Then
            msg = msg & ", " & cel.Address(False, False)
        End If
    Next cel
    If msg = "" Then
        MsgBox "No empty uncolored cells.", vbInformation
    Else
        MsgBox "The following uncolored cells are empty:" & vbCrLf & Mid(msg, 3), vbInformation
    End If
End Sub

the code work included hidden cell/rows, how to make/modify the code only work in visible cells
thanks for your time

[HansV]

Change the line

Code: Select all

        If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone Then

to

Code: Select all

        If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone And cel.Hidden = False Then

[Susanto3311]

hi hans,

after change the line with, then running with new code

If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone And cel.Hidden = False Then

show error message number "400".
how to fix it?

[HansV]

Sorry about that. Assuming that you hide (or filter) rows:

Code: Select all

        If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone And cel.EntireRow.Hidden = False Then

[Susanto3311]

hi HansV, working well,thank you

[Susanto3311]

hi hans..

i think this a long time ago , the code work properly but not work properly for merged cell
if the cell contains merged cells the code not work properly
how to code work for merged cell & for too keep visible cell
here the complete code

Sub checkemptycell()
Dim rng As Range
Dim cel As Range
Dim msg As String
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a range", Type:=8)
On Error GoTo 0
If rng Is Nothing Then
Beep
Exit Sub
End If
For Each cel In rng
If cel.Value = "" And cel.Interior.ColorIndex = xlColorIndexNone And cel.EntireRow.Hidden = False Then
msg = msg & ", " & cel.Address(False, False)
End If
Next cel
If msg = "" Then
MsgBox "No empty uncolored cells.", vbInformation
Else
MsgBox "The following uncolored cells are empty:" & vbCrLf & Mid(msg, 3), vbInformation
End If
End Sub

thank in advance.

[HansV]

In general, it is better to avoid merging cells.

Could you attach a small sample workbook that demonstrates the problem? Thanks in advance.

[Susanto3311]

hi hans, thanks a lot

but unfortunately , my excel file is confidential with vba & protected from my department
for information the range can selected in (I4:V22), into range contains hidden row and merged cell..
i hope this help you.

[HansV]

I tested on a small sample workbook with merged cells in a filtered range. The code ran without errors...
So I'm afraid I cannot help.

[Susanto3311]

hi hans..

thank you, i will try it again.