[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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
0
satmisha
Asked:
satmisha
  • 7
  • 6
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now