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

问题描述

我很熟悉VBA。我手里有工作来提高VBA代码的性能。为了提高代码的性能，我必须阅读整个行并将其与另一行进行比较。有没有办法在VBA这样做？

伪码：

  sheet1_row1 =从sheet1读取row1 
 sheet2_row1 =从sheet2读取row1 
如果sheet1_row1 = sheet2_row1然后
打印行包含相同的值
 else 
 print行包含diff值
 end if 
  

解决方案

 子checkit ）
 Dim a As Application 
 Set a = Application 
 MsgBox Join（a.Transpose（a.Transpose（ActiveSheet.Rows（1）.Value）），Chr（0））= _ 
加入（a.Transpose（a.Transpose（ActiveSheet.Rows（2）.Value）），Chr（0））
 
 End Sub 
  

发生了什么：

• 一个只是应用程序的缩写，以使代码更容易阅读


• ActiveSheet.Rows（1）.Value 返回一个尺寸为2到2的数组（1到1，1到{工作表中的列数}）


• 我们希望将上面的数组缩小为一个单一的值 Join（），所以我们可以将它与第二行的不同数组进行比较。但是，Join（）只适用于1-D数组，所以我们通过 Application.Transpose（）运行数组。注意：如果您比较列而不是行，那么您只需要一次通过Transpose（）。


• 应用 Join（）到数组给我们一个单个字符串，其中原始单元格值由空字符（ Chr（0））分隔）：我们选择这个，因为它不太可能


• 此后，我们现在有两个常规字符串，容易比较

$ b注意：正如Reafidy在评论中指出的那样， Transpose（）不能处理超过约数的数组。 65,000个元素，所以您不能使用这种方式来比较Excel版本中的两个列，其中页面的行数多于此数量（即任何非古代版本）。

注意2：与从工作表读取的数据变量数组中使用的循环相比，此方法的性能相当差。如果要逐行比较大量的行，那么上面的方法会慢得多。

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?

Pseudocode:

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

What's going on:

• 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

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).

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屋！