VBA: Show Message "Empty Data" Into A Range

Susanto3311
3StarLounger
Posts: 237
Joined: 17 Feb 2022, 05:16

VBA: Show Message "Empty Data" Into A Range

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA: Show Message "Empty Data" Into A Range

Post by HansV »

See the attached workbook.

sample vba.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 237
Joined: 17 Feb 2022, 05:16

Re: VBA: Show Message "Empty Data" Into A Range

Post by Susanto3311 »

hi Hans, thank you so much. Working well.

Susanto3311
3StarLounger
Posts: 237
Joined: 17 Feb 2022, 05:16

Re: VBA: Show Message "Empty Data" Into A Range

Post by 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

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA: Show Message "Empty Data" Into A Range

Post by 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
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 237
Joined: 17 Feb 2022, 05:16

Re: VBA: Show Message "Empty Data" Into A Range

Post by 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?

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA: Show Message "Empty Data" Into A Range

Post by 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
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 237
Joined: 17 Feb 2022, 05:16

Re: VBA: Show Message "Empty Data" Into A Range

Post by Susanto3311 »

hi HansV, working well,thank you