使用(偏移和调整大小)而不是(相交)，引发运行时错误"1004"：应用程序定义或对象定义的错误

Question

在下面的代码中，我使用Intersect来选择匹配特定条件，而不是选择整行
作为一个学习目的，我尝试使用(Offset&Resize(而不是(Intersect(，但它提高了

运行时错误"1004"：应用程序定义或对象定义错误

我在range A:AF
上发现的价值观，感谢您的宝贵意见和回答

Sub Union_Test()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim lastR As Long: lastR = ws.Cells(Rows.Count, 1).End(xlUp).Row
Dim cel As Range, rng As Range, uRng As Range
Set rng = ws.Range("V3:V" & lastR)
For Each cel In rng
If cel.value = "Yes" Then
If uRng Is Nothing Then
Set uRng = cel
Else
Set uRng = Union(uRng, cel)
End If
End If
Next cel
'If Not uRng Is Nothing Then Intersect(uRng.EntireRow, ws.UsedRange).Select 'this works perfectly
If Not uRng Is Nothing Then uRng.Offset(, -21).Resize(, 32).Select  'This raising error
End Sub

Answer 1

我修改了代码，在IF Condtion之后将Offset和resize添加到Urng本身的值，然后它就工作了。

Sub Union_Test()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim lastR As Long: lastR = ws.Cells(Rows.Count, 1).End(xlUp).Row
Dim cel As Range, rng As Range, uRng As Range
Set rng = ws.Range("V3:V" & lastR)
For Each cel In rng
If cel.value = "Yes" Then
If uRng Is Nothing Then
Set uRng = cel.Offset(, -21).Resize(, 32)
Else
Set uRng = Union(uRng, cel.Offset(, -21).Resize(, 32))
End If
End If
Next cel
If Not uRng Is Nothing Then uRng.Select    'Now This works
End Sub

Answer 2

另一个选项，创建从列A到AF的rng，然后在其.Rows上循环。

Set rng = ws.Range("A3:AF" & lastR)
Dim rw As Range
For Each rw in rng.Rows
If rw.Range("V1").Value = "Yes" Then 'This refers to V3, V4, V5, etc.
If uRng Is Nothing Then
Set uRng = rw
Else
Set uRng = Union(uRng, rw)
End If
End If
Next

Answer 3

您可以使用代码(从问题中(获得您想要的内容，只需替换其最后一行：

If Not uRng Is Nothing Then uRng.Offset(, -21).Resize(, 32).Select

它在不连续的范围内不工作，具有：

If Not uRng Is Nothing Then Intersect(uRng.EntireRow, ws.Range("A:AF")).Select

最好(更有效(在单列中从每行的一个单元格构建Union范围。对于大的此类范围(也就列而言(，它会消耗更多的Excel资源。。。