Reference a Range from a Range Object

Question

I'm trying to find a way to refer a range from another range for example, A range that holds the cells "A5:A10", 6 cells are in that range. What is needed is the range next to it which is "B5:B10".How can I refer it when there is already a range object ("A5:A10" in this case") to the range next it.

   Dim R As Range
   Dim A As Range
   Set R = R("A5:A10").Select
   Set R = 
'Code to refer to next column is here

Sorry this could be the wrong syntax to start off with , it's been a while since I coded in vba, it's just to clarify what's is needed solve this.

Does it give me the same size of the range(same amount of cells in range)? — Kozero
– Kozero, Commented Jan 9, 2016 at 8:47

teylyn · Accepted Answer · 2016-01-09 08:16:27Z

1

Try this:

Sub setRanges()
Dim ws As Worksheet
Dim rngA As Range
Dim rngB As Range

'set the worksheet -- Adjust the worksheet name as required
Set ws = ThisWorkbook.Worksheets("Sheet1")
'set the first range to a range in the worksheet
Set rngA = ws.Range("A5:A10")
' set the second range to an offest of the first range
' in this case, use an offset of one column, with the same row
' ... remember the offset command takes rows in the first parameter
' ... and the second parameter is for the columns
Set rngB = rngA.Offset(0, 1)
' so, zero row offset, i.e. stay in the same row
' and 1 column offset to get the rngB for one column to the right of rngA
rngB.Select
' don't use Select in your code. This is just to demo.

End Sub

answered Jan 9, 2016 at 8:16

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reference a Range from a Range Object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related