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
-
While this is marked as answered what do you want to do with the column once the range is defined?brettdj– brettdj2011-09-22 12:52:44 +00:00Commented Sep 22, 2011 at 12:52
-
I am working with Cognos TM1, and need to get comments on cells from OLAPjoseph– joseph2011-09-22 21:01:11 +00:00Commented Sep 22, 2011 at 21:01
Add a comment
|
3 Answers
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
3 Comments
Patrick Honorez
1) I would not use cell as a variable name. Call it myCell. 2) You need to
DIM myCell as Range
Alex K.
Added a dim, I left cell as is, it isn't a reserved word and isn't used anywhere as a class name in the xl object model (although Cells is)
Patrick Honorez
@Alex: you're are probably right for Cell. I am too cautious :-/
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
Comments
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) = ""
