Check Blank row check inbetween

Hi Experts,
I want to check whether row is blank in-between of the Range if yes throw an error message.

Attaching excel for the same.

Looking forward to hearing from you.
BlankRowCheckInbetween.xlsm
satmishaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Your macro only checks the range D5:D9.

Do you want to check if an entire row is blank/empty, or only if there are blank cells within the range?  Or something else?
0
satmishaAuthor Commented:
Glad to see your reply Glenn...

Want to check if there are blank cells within the range..
0
Glenn RayExcel VBA DeveloperCommented:
Okay, to report if there are any empty cells within a defined range, try this modification to your code:
Sub Button1_Click()
    Dim cl As Range
    Dim EmpTable As Range
    Dim boolBlank As Boolean
    
    Set EmpTable = Range("TestID")
    
    'Check whether Range is having blank row inbetween throw message if row is blank inbetween
    For Each cl In EmpTable
        If IsEmpty(cl) Then
            boolBlank = True
        End If
    Next
    If boolBlank Then
        MsgBox "There is an empty cell in this range."
    Else
        MsgBox "There are no empty cells in this range."
    End If
End Sub

Open in new window


Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Glenn RayExcel VBA DeveloperCommented:
By the way, there is a non-VBA method to determine this also using the ISBLANK function:

(entered as an array function with [Ctrl]+[Shift]+[Enter])
=OR(ISBLANK(D5:D9))

It will work with a range name (ex., "TestID") also.

To mimic the VBA code:
=IF(OR(ISBLANK(TestID)),"There is an empty cell in this range.","There are no empty cells in this range")



Regards,
-Glenn
0
satmishaAuthor Commented:
Thanks Glenn for your prompt reply.

There is small issue i.e. we need to check if there are any empty cell in-between or not but currently we are checking empty cell in entire range.

for example Range D5:D9 if D1 to D6 contains some value, D7 is empty and D9 having some value that means there is empty cell in-between.

In case if range having values from D5 to D7 and D8 and D9 are empty that means Range is not having blank cells in-between.

Hope this would clarify.

Looking forward to hearing from you.
0
Glenn RayExcel VBA DeveloperCommented:
Totally different logic needed.  Re-stating your request to make sure I understand:

You want to know if there is an empty cell(s) anywhere within a range of cells such that the data in the range is no longer contiguous (contiguous:  having no breaks, separations, divisions in the data).  Example:
D5: x
D6: x
D7: x
D8:
D9: x
 - returns "This range is not contiguous."

However,
D5: x
D6: x
D7: x
D8:
D9:
 -returns "This range is contiguous."

For a single column this is simple, but for multiple columns it becomes more complex.  Is this what you need?
0
satmishaAuthor Commented:
Thanks Glenn, Apologies for confusion. Here I am enclosing sheet to clarify further doubts which I guess couldn't
explain in my previous reply.
BlankRowCheckInbetween.xlsm
0
satmishaAuthor Commented:
Yes, Glenn.. This is exactly what I am looking for ....
0
Glenn RayExcel VBA DeveloperCommented:
Actually, your example looks like the following rule should be checked:

If a given range has blank cells at the start or any blank cells in between cells, then flag.  Otherwise, if the range is completed with values or only has blank cells at the end, then don't flag.

just need to make sure; it changes the logic test.
0
satmishaAuthor Commented:
Yes...
0
satmishaAuthor Commented:
Seems like with minor modification.. i t is working fine i.e.

Sub Button1_Click()

    Dim cl As Range
    Dim EmpTable As Range
    Dim boolBlank As Boolean
   
    Set EmpTable = Range("TestID")
   
    'Check whether Range is having blank row inbetween throw message if row is blank inbetween
    For Each cl In EmpTable
        If IsEmpty(cl) Then
            boolBlank = True
        End If
        If boolBlank And cl.Value2 <> vbNullString Then
            MsgBox "There is an empty cell in this range."
           
            Exit For
        End If
    Next
   
   ' If boolBlank Then
        'MsgBox "There is an empty cell in this range."
    'Else
       ' MsgBox "There are no empty cells in this range."
    'End If

End Sub
0
Glenn RayExcel VBA DeveloperCommented:
That code looks good to me.  Seems to work on several examples and meets your conditions.  Good work!

-Glenn
0
satmishaAuthor Commented:
Thanks a lot Glenn for your valuable input.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.