Select Different Column Range Excel VBA

1
votes

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

2
excelvbarange
Columns(1).Select – xQbert
for single cells Cells([row],[column]) (like Cells(7, 2) would be B7) – Dirk Reichel
Consider reading this. 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
Don't do that. @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

2 Answers

0
votes

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

0
votes

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