vba - 使用循环格式化所有空行

Question

试图找出如何在 Excel 中循环以下 VBA 宏。我需要找到所有空行并格式化它们(而不是像其他建议中那样删除或隐藏它们)，如您在下面的现有代码中所见:

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveCell.Offset(, 0).Resize(1, 14).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249946592608417
    .PatternTintAndShade = 0
End With

我想循环这个直到所有空行都被格式化。

Answer 1

尝试这个:

Sub Test()

Dim sht As Worksheet, lastrow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
    If Application.CountA(Range("A" & i & ":N" & i).EntireRow) = 0 Then
        With Range("A" & i & ":N" & i).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
            .PatternTintAndShade = 0
        End With
    End If
Next i

End Sub

尽管我同意您最好的解决方案是使用条件格式:)