VBA to validate if non-continuous range contains values

Question

I'm trying to create a macro to validate if a non continuous range is filled out and then delete empty rows after the last row that contains values.

In the above image, Columns A, D, G, H, I are mandatory fields. If one contains a value the rest of the mandatory fields must also contain a value. The green highlighted cells are an example of inputs that would pass validation, whereas the yellow would need to fail as the value in A and G are not filled out.

I've tried the below:

Sub Validate()
Dim Cell As Range
For Each Cell In Union(Range("A2:A3"), Range("D2:D3"), Range("G2:G3"), Range("H2:H3"), Range("I2:I3"))
    If IsEmpty(Cell) Then
        MsgBox ("Error: All mandatory fields must be filled out") 'actions To Do If True
End If
Next
End Sub

The problem is that the number of rows will be different every time a user inputs information and once they are done they will leave the rest of the rows blank. I'm not too sure how to proceed as I'm pretty new to VBA and coding in general. I'm hoping a loop of some sort that checks for these mandatory fields and stops once it encounters an empty row will do the trick, but then again I really don't know much about coding.

I found VBA to delete empty rows, shown below, and would like to have that run after the validation completes.

Unfortunately the code sample tool wasn't cooperating so I've included an Image.

Not sure if any of that made any sort of sense, if you need clarification please let me know. Any and all help is greatly appreciated!

So, if any row there is an entry in any of column A,D,G,H or I then there must be an entry in all? Is there any data in any other columns? — SJR
– SJR, Commented Apr 10, 2017 at 19:32
Correct, if there is an entry in any of column A,D,G,H or I then there must be an entry in all. There could also be values in columns B, C, E, F, J, or K, but are not mandatory. K would be the last column that could contain values. — JC3
– JC3, Commented Apr 10, 2017 at 19:38

SJR · Accepted Answer · 2017-04-10 19:50:16Z

2

OK, give this a try. I'm not exactly sure what you want to happen when you find an invalid row - this just displays a message box.

Sub x()

Dim rFind As Range, r1 As Range, r As Long

With Sheet1
    Set rFind = .Cells.Find(What:="*", After:=.Cells(1), LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False)

    If Not rFind Is Nothing Then
        For r = 2 To rFind.Row
            Set r1 = Union(Cells(r, "A"), Cells(r, "D"), Cells(r, "G"), Cells(r, "H"), Cells(r, "I"))
            If WorksheetFunction.Count(r1) > 0 And WorksheetFunction.CountA(r1) < r1.Count Then
                MsgBox r & " incomplete"
            End If
        Next r
    End If
End With

End Sub

answered Apr 10, 2017 at 19:50

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JC3 Over a year ago

Thank you so much! This is exactly what I was looking for, the one question I have left would be how to incorporate the delete empty rows sub after the validation completes.

SJR Over a year ago

Glad it worked. Looks to me as if that code will delete any row which contains a blank, but if you're saying that blanks are legitimate in some columns don't think you want that? If you have blanks after running the code above, don't you want the user to be able to fill them before doing any deleting (in which case what exactly will you be deleting?)

JC3 Over a year ago

Yeah, that was a bit of an oversight on my part. I forgot that I used that sub for the user to clear the sheet to start over. Essentially what I was after was for a way, after the validation passes, to delete everything after the last filled out row. So say in the above picture, if all four rows were filled out correctly to delete row 6 and down.

Collectives™ on Stack Overflow

VBA to validate if non-continuous range contains values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related