Search code examples
excelvbaoffset

using (Offset & Resize) instead of (Intersect),raising Run-time error '1004':Application-defined or object-defined error


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

Solution

  • 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