CODEWITHSUNDEEP
vbaexcel
Matthew Ingle
Matthew Ingle

Reputation: 119

Excel VBA If row has data, cell cannot be blank

I have searched for this answer, but can't quite find what I need. I have a document in which users will be pasting data, sometimes up to 5000 rows (that's the limit). It has columns A-AG. Some of the columns are required, some are not. I have been told users may post data with blank rows.

I am trying to work on a macro to alert users to missing data. I will tie this to a user form box. The way I was thinking to tackle this problem would be to loop through rows from 19 (where the data starts) to 5000. If the row has any data, then check the required column.

For example, check row 19, if there is any data, check column F. If data is missing in column F, generate message Box. I hope that makes sense, and any help would be greatly appreciated.

Upvotes: 0

Views: 3299

Answers (3)

Comintern
Comintern

Reputation: 22185

The other answers work, but are less efficient due to having the key rule reversed. There's no reason to check if a row is empty unless one of the compulsory fields is missing. From your question, the rule is:

Rows must have all compulsory fields unless they are empty.

This can be restated as:

If any compulsory field is empty, the entire row must be empty.

The programming test for the second is much, much more efficient because you can short-circuit if the first part of the test is true:

Sub CheckRows()
    Dim r As Long, c As Long, missingValue As Boolean

    With ActiveSheet
        For r = 1 To 5000
            Select Case True
                Case .Cells(r, "F") = vbNullString:
                    missingValue = True
                Case .Cells(r, "G") = vbNullString:
                    missingValue = True
                Case .Cells(r, "J") = vbNullString:
                    missingValue = True
                Case Else
                    missingValue = False
            End Select
            'This is the ONLY CASE where you need to check if the row is empty.
            If missingValue Then
                If WorksheetFunction.CountA(.Range(.Cells(r, 1), .Cells(r, 33))) > 0 Then
                    MsgBox "Row " & r & " has some compulsory cells not filled in"
                End If
            End If
        Next
    End With
End Sub

Upvotes: 2

YowE3K
YowE3K

Reputation: 23974

The following code will check each of the rows from 19 to 5000, see if there is any non-empty cell in the first 33 columns (i.e. A to AG), then check certain cells to make sure that they are not empty. (I arbitrarily decided that columns F, G and J were compulsory.)

Sub CheckRows
    Dim r As Long
    Dim c As Long
    'Dim emptyRow As Boolean

    With ActiveSheet
        For r = 19 to 5000
            'Edited based on suggestion by Scott Craner
            If WorksheetFunction.CountA(.Range(.Cells(r, 1), .Cells(r, 33))) > 0 Then
            'emptyRow = True
            'For c = 1 To 33
            '    If Not IsEmpty(.Cells(r, c)) Then
            '        emptyRow = False
            '        Exit For
            '    End If
            'Next
            'If Not emptyRow Then
                If IsEmpty(.Cells(r, "F")) Or _
                   IsEmpty(.Cells(r, "G")) Or _
                   IsEmpty(.Cells(r, "J")) Then
                    MsgBox "Row " & r & " has some compulsory cells not filled in"
                End If
            End If
        Next
    End With
End Sub

Upvotes: 2

Matt Klein
Matt Klein

Reputation: 8424

Try checking all cells in a row first, and if any data is found, then check the required items. Something like this (my VBA is pretty rusty, so this may not be copy-pastable, but it should be close):

Dim row As Integer, col As Integer, hasData As Boolean
Dim errors() As String, errorText as String

ReDim errors(1 To 1) As String

For row = 19 To 5000
    hasData = False
    ` Check each cell in this row to see if it has data
    For col = 1 To 33
        If IsEmpty(Cells(row, col).Value) = True Then
            hasData = True
            Exit For
        End If
    Next col

    If hasData Then

        errorText = ""

        `validate data here for this row
        `set errorText to non-empty if an error found

        If errorText <> "" Then
            errors(UBound(errors)) = errorText
            ReDim Preserve errors(1 To UBound(errors) + 1) As String
        End If
    End If
Next row

`if errors found, join all the errors together and show to user
If UBound(errors) > 1 Then MsgBox Join(errors, vbCrLf)

Upvotes: 0

Related Questions