CODEWITHSUNDEEP
excelvba
joseph
joseph

Reputation: 13

VBA how to loop through a column in Excel

I know start cell, and I need to go down through the column. And I need to exit cycle when the next cell is empty. How to do it in VBA code?

Thanks for replies

Upvotes: 1

Views: 8254

Answers (3)

Alex K.
Alex K.

Reputation: 175748

How about;

'//get a range from anchor cell (a1) to the 1st empty cell in the same column;
dim r As Range, cell as Range
set r = Range(Range("A1"), Range("A1").End(xlDown))

'//loop it
for Each cell In r
    msgbox cell.Value
next

Upvotes: 3

iDevlop
iDevlop

Reputation: 25252

I adjusted AlexK's answer:

dim c As Range

'//loop it
for Each c In Range(Range("A1"), Range("A1").End(xlDown))
    msgbox c.Value
next

Upvotes: 2

Xophmeister
Xophmeister

Reputation: 9211

In VBA, everything cell-based can be done with ranges, using offsets to return the value you are looking for:

Dim Anchor as Range

Set Anchor = Range("A1")

i = 0
Do 
  ' Cell in column is Anchor.Offset(0, i)
  ' i.e., Anchor.Offset(0, 0) = A1
  '       Anchor.Offset(0, 1) = B1
  '       Anchor.Offset(0, ") = C1
  '       etc.

  ' Do whatever you like with this, here!

  i = i + 1
Loop Until Anchor.Offset(0, i) = ""

Upvotes: 0

Related Questions