excel - 如何检查两个范围值是否相等

Question

如果整行中有相同的值，我想合并列中的单元格。
例如。如果 A1:G1 范围与 A2:G2 相同，我想将 A1:A2 单元格、B1:B2 合并到 G1:G2。
使用下面的代码，我得到运行时错误 13：类型不匹配。我假设，这个问题在于检查两个范围的相等性。

Dim i As Long, j As Long, row as Long
row = Cells(Rows.Count, 6).End(xlUp).row
For i = row To 7 Step -1
        If Range(Cells(i, 7), Cells(i, 24)).Value = Range(Cells(i - 1, 7), Cells(i - 1, 24)).Value Then
        For j = 7 To 24 Step 1
            Range(Cells(i, j), Cells(i - 1, j)).Merge
        Next j
        End If
Next i

问题是，如何检查两个范围值是否相等？

评论后编辑：使用下面的代码，它实际上可以工作

Dim i As Long, j As Long, row As Long
row = Cells(Rows.Count, 6).End(xlUp).row
For i = row To 7 Step -1
        If Join(Application.Transpose(Application.Transpose(Range(Cells(i, 7), Cells(i, 24)))), Chr(0)) = Join(Application.Transpose(Application.Transpose(Range(Cells(i - 1, 7), Cells(i - 1, 24)))), Chr(0)) Then
        For j = 7 To 24 Step 1
            Range(Cells(i, j), Cells(i - 1, j)).Merge
            Application.DisplayAlerts = False
        Next j
        End If
Next i

但是，我想知道，为什么您（@Pᴇʜ）将第一行和最后一行的函数分开。

此外，使用我的代码，在没有合并单元格的情况下，我有 te 循环来更改单元格颜色：

Dim row As Long
row = Cells(Rows.Count, 6).End(xlUp).ro
Do Until IsEmpty(Cells(row, 3))
     If row Mod 2 <> 0 Then
       Range(Cells(row, 3), Cells(row, 24)).Interior.Color = RGB(217, 225, 242)
     Else
       Range(Cells(row, 3), Cells(row, 24)).Interior.Color = xlNone
     End If
     row = row + 1
Loop

合并单元格后如何处理？

标签： excelvbarangeequals

Answer 1

问题是……

Range(Cells(i, 7), Cells(i, 24)).Value

返回值数组，但不能将值数组与=. 因此，您需要遍历所有这些值并将每个值与相应的值进行比较

Range(Cells(i - 1, 7), Cells(i - 1, 24)).Value

由于您已经有了这个循环，只需移动您的If语句以将其检查到循环中：

Dim iRow As Long, iCol As Long, LastRow as Long
LastRow = Cells(Rows.Count, 6).End(xlUp).row
For iRow = LastRow To 7 Step -1
    For iCol = 7 To 24 Step 1
        If Cells(iRow, iCol).Value = Cells(iRow - 1, iCol).Value Then
            Range(Cells(iRow, iCol), Cells(iRow - 1, iCol)).Merge
        End If
    Next iCol 
Next iRow 

请注意，我将变量命名更改为更有意义的名称。这也避免了使用RowExcel 本身经常使用的变量名。

---

根据评论编辑

Option Explicit

Sub Test()
    Dim RangeToMerge As Range
    Set RangeToMerge = Range("C5:F14")

    Dim FirstMergeRow As Long
    FirstMergeRow = 1

    Dim iRow As Long, iCol As Long
    For iRow = 1 To RangeToMerge.Rows.Count - 1
        If Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(FirstMergeRow).Value)), "|") <> _
           Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(iRow + 1).Value)), "|") Then
            If iRow <> FirstMergeRow Then
                For iCol = 1 To RangeToMerge.Columns.Count
                    Application.DisplayAlerts = False
                    RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
                    Application.DisplayAlerts = True
                Next iCol
            End If
            FirstMergeRow = iRow + 1
        End If
    Next iRow

    'merge last ones
    If iRow <> FirstMergeRow Then
        For iCol = 1 To RangeToMerge.Columns.Count
            Application.DisplayAlerts = False
            RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
            Application.DisplayAlerts = True
        Next iCol
    End If
End Sub

会转以下

进入