CODEWITHSUNDEEP
vbaexcel
riyas202
riyas202

Reputation: 15

How do I set a Range variable, using two other Range variables?

In my Excel macro, I have two Range Datatype variables

I need a third Range variable DataRng which will store the range from start to end (i.e Range("A1:C10")).

Is there anyway I can do this?

Upvotes: 0

Views: 390

Answers (3)

Nicolaesse
Nicolaesse

Reputation: 2714

In addition of the @user3598756 answer (which is OK), you can also use this:

Range(Cells(1,1), Cells(10,3)).Select

Personally I prefear this one because you can use more easily some index inside the Cells formula.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

The technical aspect on how to set the Range was given in the answer above by @user3598756

I also suggest (in case you are not already familiar) to fully qualify your Ranges, with the relevant worksheet where these ranges are located.

So, you should add With Worksheets("YourSheetName") before setting-up the ranges, and inside the With statement, qualify your Range by adding a . before.

Code 

Option Explicit

Sub DefineCombinedRange()

Dim RangeStrt As Range
Dim RangeEnd  As Range
Dim DataRng  As Range

' add With statement to fully qualify your Ranges
With Worksheets("Sheet1") '<-- modify "Sheet1" with your sheet's name (where you have your ranges)
    Set RangeStrt = .Range("A1")
    Set RangeEnd = .Range("C10")

    Set DataRng = Range(RangeStrt, RangeEnd) '<-- setting the 3rd range, using the first 2 Ranges

    ' the line below is for debug
    Debug.Print DataRng.Address
End With

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

Use

Set dataRng = Range(RangeStrt, RangeEnd)

Upvotes: 1

Related Questions