excel - 如何在VBA循环中删除除A列之外的整行？

Question

如果 A 列中的值以“ABC”开头并删除该单元格右侧的所有内容，我将尝试突出显示整行灰色。关于如何做到这一点的任何想法？

Dim DataRange As Range
Set DataRange = Range("A1:U" & LastRow)

Set MyRange = Range("A2:A" & LastRow)

For Each Cell In MyRange
If UCase(Left(Cell.Value, 3)) = "ABC" Then
    Cell.EntireRow.Interior.ColorIndex = 15


Else


End If
Next

标签： excelvba

Answer 1

这是非常简单的方法：

Dim lastRow As Long
Dim row As Long
Dim temp As String

' insert your sheet name here
With ThisWorkbook.Worksheets("your sheet name")

    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' you can change the starting row, right now its 1
    For row = 1 To lastRow

        ' store whats in col A in a temporary variable
        temp = Trim(CStr(.Range("A" & row).Value))

        ' if col A isn't 'ABC' clear & grey entire row
        If UCase(Left(.Range("A" & row).Value), 3) <> "ABC" Then

            .Rows(row).ClearContents
            .Rows(row).Interior.ColorIndex = 15

            ' place temp variable value back in col A and make interior No Fill
            .Range("A" & row).Value = temp
            .Range("A" & row).Interior.ColorIndex = 0

        End If
    Next
End With