CODEWITHSUNDEEP
excelvba
siddharth_001
siddharth_001

Reputation: 23

how to specify a range in vba using a cell value in excel

I am very new to vba and basically only use it when amending a range value (having previously recorded the script using a macro in excel). Since I add/delete rows in excel I have to go to the vba script and manually update the range part. So if my range on the excel sheet has expanded from A2:R83 to A2.R84 heres one of the parts I update from:

Range("A2:R83").Select

To:

Range("A2:R84").Select

Is there a way I can specify a cell that vba can take the range values from? eg can I, on the excel sheet cell X1 input A2 and in cell Y2 input R84 and have the vba script reference these cells to determine the current range?

Appreciate any help!

Upvotes: 2

Views: 27662

Answers (3)

Excel Sigma
Excel Sigma

Reputation: 11

I found that it is possible to make the validation range dinamic using INDIRECT.

1.- In location that you choose (in the example I use X1 in sheet1) put 

="'Sheet1'!"&"A2:"&"R"&COUNTA(R1:R2000)

I put R2000 to have plenty space in case the range grows, change to a size that suits you. The result of this formula will be a range. Its size will change every time you put something new in R because of the Counta.

2.- In the validation section place this formula when you record it. 

=INDIRECT('Sheet'!$X$1)

This makes the validation read the range based on what x1 says.

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6982

This will figure your range,

Sub SelectRng()
    Dim Rws As Long, Rng As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range(Cells(2, "A"), Cells(Rws, "R"))


    Rng.Select    'or whatever you want to do with it.


End Sub

Upvotes: 0

eirikdaude
eirikdaude

Reputation: 3255

I believe this will do what you want, :

Sub test()
  Dim s1 As String, s2 As String

  s1 = Sheet1.Range("A1"): s2 = Sheet1.Range("B1")

  Range(s1 & ":" & s2).Select
End Sub

You will, however, run into trouble if the values in A1 and B1 are not valid cell-names, so some input validation may be a good idea.

Upvotes: 4

Related Questions