识别并突出显示Excel VBA中的空白行 [英] Identifying and highlighting a blank row in Excel VBA

问题描述

场景:每行将包含23列；20个将包含用户填充的数据，最后3个将通过vba自动生成.运行时，如果vba代码将任何行的前20列标识为空白单元格，则整行将声明为空白并突出显示.

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

推荐答案

我使用了

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

如果所有20个单元格均为空白，则将整行设置为空白，并应用黄色突出显示.

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

我正在使用 COUNTBLANK函数，因为尚不清楚公式是否返回零长度的字符串.COUNTBLANK会将其计为空白.

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.

这篇关于识别并突出显示Excel VBA中的空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！