如何比较工作表中的两整行 [英] How to compare two entire rows in a sheet

问题描述

我是 VBA 新手.我手头有工作来提高 VBA 代码的性能.为了提高代码的性能，我必须读取整行并将其与另一行进行比较.有没有办法在 VBA 中做到这一点?

I am new to VBA. I have job in my hand to improve performance of VBA code. To improve performance of the code, I have to read entire row and compare it with another row. Is there any way to do this in VBA?

伪代码:

sheet1_row1=read row1 from sheet1
sheet2_row1=read row1 from sheet2
if sheet1_row1 = sheet2_row1 then
      print "Row contains same value"
else
      print "Row contains diff value"
end if

推荐答案

Sub checkit()
Dim a As Application
Set a = Application
MsgBox Join(a.Transpose(a.Transpose(ActiveSheet.Rows(1).Value)), Chr(0)) = _
       Join(a.Transpose(a.Transpose(ActiveSheet.Rows(2).Value)), Chr(0))

End Sub

发生了什么:

• a 只是 Application 的简写，使下面的代码更易于阅读
• ActiveSheet.Rows(1).Value 返回维度为(1 到 1，1 到 {工作表中的列数})的二维数组
• 我们想使用 Join() 将上面的数组压缩为单个值，以便我们可以将它与第二行的不同数组进行比较.但是，Join() 仅适用于一维数组，因此我们通过 Application.Transpose() 运行该数组两次.注意:如果您比较的是列而不是行，那么您只需要通过 Transpose() 一次.
• 将 Join() 应用于数组为我们提供了一个字符串，其中原始单元格值由空字符"(Chr(0)) 分隔:我们选择它是因为它不可能出现在任何单元格值本身中.
• 在此之后，我们现在有两个易于比较的常规字符串

• a is just shorthand for Application to keep the code below easier to read
• ActiveSheet.Rows(1).Value returns a 2-D array with dimensions (1 to 1, 1 to {number of columns in a worksheet})
• We'd like to condense the array above into a single value using Join(), so we can compare it with a different array from the second row. However, Join() only works on 1-D arrays, so we run the array twice through Application.Transpose(). Note: if you were comparing columns instead of rows then you'd only need one pass through Transpose().
• Applying Join() to the array gives us a single string where the original cell values are separated by a "null character" (Chr(0)): we select this since it's unlikely to be present in any of the cell values themselves.
• After this we now have two regular strings which are easily compared

注意:正如 Reafidy 在评论中指出的那样，Transpose() 不能处理超过大约的数组.65,000 个元素，因此您不能使用这种方法来比较 Excel 版本中的两整列，其中工作表的行数超过此数量(即任何非古代版本).

Note: as pointed out by Reafidy in the comments, Transpose() can't handle arrays with more than approx. 65,000 elements, so you can't use this approach to compare two whole columns in versions of Excel where sheets have more than this number of rows (i.e. any non-ancient version).

注意 2:与在从工作表读取的数据的变体数组上使用的循环相比，此方法的性能非常差.如果您要逐行比较大量的行，那么上面的方法会慢很多.

Note 2: this method has quite bad performance compared to a loop used on a variant array of data read from the worksheet. If you're going to do a row-by-row comparison over a large number of rows, then the approach above will be much slower.

这篇关于如何比较工作表中的两整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！