如何比较工作表中的两整行 [英] 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 forApplication
to keep the code below easier to readActiveSheet.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 throughApplication.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屋!