CODEWITHSUNDEEP
vbaexcel
kgkmeekg
kgkmeekg

Reputation: 534

Identifying and highlighting a blank row in Excel VBA

Scenario: Each row would contain 23 columns; 20 would contain user populated data and the last 3 would be autogenerated through vba. While running if the vba code identifies the first 20 columns of any row to be blank cells then the whole row is declared blank and highlighted.

I have been able to write the following code:

 For Each rng In Range("A1:A" & LastRow)
    With rng
    If .Value < 1 Then
        MsgBox "Blank Cell found"
        blnkcnt = 0
    For Each mycl In Range("A" & ActiveCell.Row & ":T" & ActiveCell.Row)
        With mycl
            If .Value < 1 Then
                blnkcnt = blnkcnt + 1
            End If
        End With
    Next mycl


    If blnkcnt = 20 Then
        lCount = lCount + 1
        With .EntireRow.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
End If
End With
Next rng

If lCount > 0 Then
   MsgBox "Data contains blank row(s): " & lCount
End

Else
    MsgBox "No blank Rows"
End If

Upvotes: 0

Views: 581

Answers (2)

user4039065
user4039065

Reputation:

I've used a COUNTBLANK function on the first 20 columns of each row to determine if any blank cells exist.

Dim rng As Range, lCount As Long, LastRow As Long

With ActiveSheet    'set this worksheet properly!
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For Each rng In .Range("A1:A" & LastRow)
        With rng.Resize(1, 20)
            If Application.CountBlank(.Cells) = 20 Then  'All 20 cells are blank
                lCount = lCount + 1
                .EntireRow.ClearContents
                .EntireRow.Interior.ColorIndex = 6
            End If
        End With
    Next rng
End With

If lCount > 0 Then
    MsgBox "Data contains blank row(s): " & lCount
Else
    MsgBox "No blank Rows"
End If

If all 20 cells are blank then the entire row is made blank and yellow highlighting is applied.

I'm using the COUNTBLANK function as it was not clear on whether you have zero-length strings returned by formulas. COUNTBLANK will count these as blanks.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149305

Don't use that. Use CountA to check if there is any data in those 20 columns.

See this (untested)

Dim ws As Worksheet
Dim i As Long

'~~> Change this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    '~~> Find lastrow and change accordingly
    '~~> For demonstration purpose using a hard coded value
    lastrow = 10

    For i = 1 To lastrow
        '~~> Use CountA to check if there is any data
        If Application.WorksheetFunction.CountA(.Range("A" & i & ":T" & i)) = 0 Then
            MsgBox "Row " & i & " is blank"
            '
            '~~> Rest os your code
            '
        End If
    Next i
End With

Upvotes: 1

Related Questions