Select Different Column Range Excel VBA

Question

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

Answer 1

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 :)

Answer 2

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