excel - 选择范围的问题可能是因为最后一列和最后一行设置不正确

Question

我正在尝试选择范围并为所有颜色单元格粘贴 0。

我不明白为什么这段代码不起作用。我浏览了论坛中的几个选项，但仍然无法正常工作。我很确定这与最后一行和最后一列有关，这是显而易见的。

你能告诉我我在这里做错了什么吗？

Dim cell As Range, rng As Range
    Dim LRRow As Long, LRCol As Long

        With ThisWorkbook.Worksheets("Data")

         LRRow = .Cells(4, .Columns.Count).End(xlToLeft).Column
         LRCol = .Cells(.Rows.Count, LRRow).End(xlUp).row

        Set rng = .Range(Cells(7, 4), Cells(LRRow, LRCol))


       ' Selection.AutoFilter
       ' Range("A1").Select


        For Each cell In rng

            If cell.Interior.Color = RGB(255, 204, 204) And cell.Value = "" Then
             cell.Value = 0
            End If

        Next cell

        End With

标签： excelvba

Answer 1

尝试改变：

 LRRow = .Cells(4, .Columns.Count).End(xlToLeft).Column
 LRCol = .Cells(.Rows.Count, LRRow).End(xlUp).row

至

LRRow = .Cells(.Rows.Count, LRRow).End(xlUp).row
LRCol = .Cells(4, .Columns.Count).End(xlToLeft).Column