CODEWITHSUNDEEP
vbaexcel
ChrisBull
ChrisBull

Reputation: 467

Create a range from offsets

I need to select a range in relation to a certain cell.

This is what i have so far - the user double clicks a cell:

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

The cell the user clicked is then available in Target as a Range.

I then need to select a new Range relative to the position of Target. I have tried the code below: 

dataSheet.Range(Target.Offset(2, 2)), Target.Offset(15, bHeight)).Select

I've tried a lot of different ways from reading other people posts using .Cells, .Address, but can't seem to get it to work. I think I'm getting the data types mixed up? Any Ideas?

Upvotes: 1

Views: 80

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can combine Offset and Resize to achieve your outcome. First use Offset to get the displacement from Target and then Resize to increase the range to the required number of rows and columns. Here is example code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim rngNew As Range
    Dim bHeight As Integer

    bHeight = 3
    Set rngNew = Target.Offset(2, 2).Resize(13, bHeight)

    rngNew.Select

End Sub

Upvotes: 2

Related Questions