1

So in excel vba I'm trying to select a range, but a different range every time. I have a loop going, but I was wondering how I would actually write the code to change the range. This is what it would look like for a single range. 

Range("B7").Select

Is there a way to do this with integers instead of strings such as "B7"

i.e 

Range(i).Select

I need it to select a single column. Any advice would be appreciated.

Thanks

bugsyb
  • 5,662
  • 7
  • 31
  • 47
  • 2
    `Columns(1).Select` – xQbert Jul 05 '16 at 17:26
  • for single cells `Cells([row],[column])` (like `Cells(7, 2)` would be B7) – Dirk Reichel Jul 05 '16 at 17:35
  • 1
    Consider reading [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Using `.Select` can cause headaches in your code. What @xQbert will certainly work if you are trying to select a whole column. Say you wanted to change the range but only to loop through cells in one column, you could use something like `Range("B" & i)` – Kyle Jul 05 '16 at 17:35
  • [Don't do that](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). @xQbert is on it, use the column directly. You could also do `Range("B7").EntireColumn` or with a variable, `Dim myCol as Long // myCol = Range("B7").Column` But you don't "really" want to select the column, but *do* something with that column. Check out the Thread I linked to, it will save you many headaches and help your code run faster. – BruceWayne Jul 05 '16 at 17:39

2 Answers2

0

Well, if you only have to selct one Cell:

Cells(y, x)

But because you have to select more:

Dim testRange As Range

   Set testRange = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1, 1), Worksheets("Sheet1").Cells(100, 1))

   testRange.Select 'Optional
   testRange = "If you read this, you are awsome!"

Including that you want a loop:

Dim testRange As Range
Dim x as Integer

  For x = 1 To n 'n = whatever you want
     Set testRange = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1, x), Worksheets("Sheet1").Cells(100, x))

     testRange.Select 'Optional
     testRange = "If you read this, you are even more awesome!" 'Fills 100x100 Cells with this text
  Next x

I hope that's helpful :)

Benno Grimm
  • 533
  • 1
  • 8
  • 16
0

If you know where the cell is relative to the starting row you could use offset to do something like this:

dim rng as Range
dim i as integer
set rng = range("B7")
for i=0 to 10
   rng.offset(0,i).select
next i

Look up offset to learn how to modify this to suit your needs

nick
  • 165
  • 6