on below code, I am using Intersect
to select matching the specific condition instead of selecting the Entire Row.
as a learning purpose, I tried to use (Offset & Resize) instead of (Intersect), But it raising
Run-time error '1004':Application-defined or object-defined error
my values found on range A:AF
In advance grateful for useful comments and answer.
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
CodePudding user response:
I modified the code to add Offset and resize to the value of Urng
itself after IF Condtion
,Then it works.
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
CodePudding user response:
Another option, creating rng
spanning from columns A to AF, then looping over its .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
CodePudding user response:
You can obtain what you want using your code (from the question), only replacing of its last line:
If Not uRng Is Nothing Then uRng.Offset(, -21).Resize(, 32).Select
which does not work in discontinuous ranges, with:
If Not uRng Is Nothing Then Intersect(uRng.EntireRow, ws.Range("A:AF")).Select
It is better (more efficient) to build a Union
range from a cell per row, in a single column. For large such ranges (in terms of columns, too) it consumes more Excel resources...