在下面的代碼中，我使用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
uj5u.com熱心網友回復：
我修改了代碼，在 之后添加了 Offset 和 resize 到Urng自身的值IF Condtion，然后它就可以作業了。
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
uj5u.com熱心網友回復：
另一種選擇，創建rng從 A 列到 AF 的跨度，然后回圈其.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
uj5u.com熱心網友回復：
您可以使用您的代碼（來自問題）獲得您想要的內容，只需替換其最后一行：
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 資源......
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/449931.html
上一篇：特定邊框的VBA條件格式