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!!!

 

[jindon]

Arguments in Resize method shoud be equal or greater than 1 (default value)
Offset first

Range("C3").Offset(,-1).Resize(2,2)

 

[h711]

cool, I know both offset and resize, but didn't thought about put them together.

I like this place, ppl always have all kinds of ideas to solve problems.

Thanks.

 

[NessPJ]

I know this is an old thread, but i'm looking for a way to do something with the above code.

I use a piece of code with Resize( , ).Offset( , ) as well.

If i have a piece of code that would end in Cell B2 and i wanted to select A2, B2 and C2 i would use the following:

Offset(0, -1).Resize(1, 3).

But what would i have to use if i end in Cell B2 and i would want to select A2, C2 and D2? Is it possible to skip a cell in the selection?

 

[mikerickson]

Offset and Resize always return a continous rectangular range.

 

[RoryA]

You would have to use Union too:

Union(offset(0, -1), offset(0, 1).resize(, 2))

 

[NessPJ]

Hey,

Thanks a lot for your reply.
My question was regarding the following code i have:

    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
                Found.[B]Union(Offset(0, -1), Offset(0, 1).Resize(, 2)).[/B]Cut _
                    Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
                Set Found = .FindNext
            Loop Until Found Is Nothing
        End If
    End With

But i get a Debug error now on that very line, when i try to run it.
Any suggestions?

 

[Sektor]

Sub CoolResize()
    Range("C3")(0, 0).Resize(2, 2).Select
End Sub

 

[Andrew Poulsom]

More like:

Union(Found.Offset(0, -1), Found.Offset(0, 1).Resize(, 2))

 

[NessPJ]

More like:

Union(Found.Offset(0, -1), Found.Offset(0, 1).Resize(, 2))

Thanks, but appearantly the Cut command will not work this way.
If i use Copy instead my code will get stuck repeating itself (because the GND value it is looking for will never dissapear.

Would it be possible to do a Copy of the selected Cells, Paste them according to the code and then Delete the source cells before the repeat?