VBA: How do I use negative range for the .Resize function?

[h711]

Range("C3").Resize(2,2)
covers C3,C4,D3,D4

But what if I want to still use C3 as reference point to cover B2, B3, C2, C3?
Range("C3").Rasize(-2,-2)
does NOT work and give error.

Any ideas? Thanks for the help!!!

 

[Andrew Poulsom]

Try:

With Union(Found.Offset(0, -1), Found.Offset(0, 1).Resize(, 2))
    .Copy Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
    .ClearContents
End With

 

[NessPJ]

Thanks a lot for your reply! The code reads like it should work fine, yet it seems to be constantly looping still (freezing my Excel).

So, this is how it looks now:

Dim Found As Range
 
Const SrchCol As String = "H"
Const SrchTxt As String = "GND"
 
With Columns(SrchCol)
Set Found = .Find(What:=SrchTxt, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Found Is Nothing Then
Do
With Union(Found.Offset(0, -1), Found.Offset(0, 1).Resize(, 2))
.Copy Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
.ClearContents
End With
Set Found = .FindNext
Loop Until Found Is Nothing
End If
End With

 

[RoryA]

You are not clearing the found cell, so naturally you will keep finding it...

 

[NessPJ]

I got it working now, using the following changes.

    Dim Found As Range
    
    Const SrchCol As String = "H"
    Const SrchTxt As String = "GND"
    
    With Columns(SrchCol)
        Set Found = .Find(What:=SrchTxt, After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not Found Is Nothing Then
            Do
            With Union(Found.Offset(0, -1), Found.Offset(0, 1).Resize(, 2))
                .Copy Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
                Found.Offset(0, -1).Resize(, 4).ClearContents
            End With
            
                Set Found = .FindNext
            Loop Until Found Is Nothing
        End If
    End With

Thanks for all the help!

 

[Peter_SSs]

It really would help if you had stuck to the original thread. It makes life difficult for helpers if you have multiple threads running on the same code. Please refer to the Posting Guidelines, especially points 21-23 and also the Forum Rules, especially #9.