1

Is it possible to define a range in VBA based on a value given in a cell?

For example, I have a dataset with four columns and a constantly changing number of rows. I have the number of rows in cell F2. Suppose cell F2 indicates the number of rows is 385, then I be able to Range("A1:D385").Select, but I want the selection of the last cell to be dependent on cell F2.

So if I change F2 to 50, that the next time I run the macro, A1:D50 will be selected, but since I'm new to VBA I can't figure it out.

pnuts
  • 58,317
  • 11
  • 87
  • 139
robinmvanb
  • 11
  • 1
  • 1
  • 3
  • 3
    try `Range("A1:D" & Range("F2").Value2).Select`. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. –  Sep 22 '15 at 12:52
  • 1
    @user3290799 Why not use `Value2`? It's quicker. – Rory Sep 22 '15 at 12:59
  • You're right @Rory. Never mind. – RTrain3k Sep 22 '15 at 13:03

2 Answers2

1

The most proper way to do this would be like this

Sub getRng()
Dim Cval As Variant
Dim Rng1 As Range
Cval = ActiveSheet.Range("F2").Value
Set Rng1 = ActiveSheet.Range("A1:D" & Cval)
End Sub

this sets Rng1 as an object that you can use later on in another function

such as

Rng1.Select

or

Rng1.Copy

Luuklag
  • 3,897
  • 11
  • 38
  • 57
user3005775
  • 306
  • 1
  • 3
  • 14
1

This is what you are looking for:

Dim lastRow As Integer

With ThisWorkbook.Sheets(1)
    lastRow = .Cells(.Rows.Count, "F").End(xlUp).row
    .range("A1:D" & lastRow).Select
End With

The code looks for the first non-empty cell in column F, from bottom to top. Similar to selecting the last cell in column F and pressing Ctrl + up-arrow.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
SimpleMethods
  • 26
  • 1