For loop in excel VBA

Question

Im trying to use For loop on an excel column. This is my code:

   For Each c In Worksheets("sheet1").Range("A1:A5000").Cells
        c.Offset(0, 1).Range("A1").Value = Right((Left(c, 13)), 7)
   Next

it works ok the problem is with the

Range("A1:A5000")

my sheet has less then 1000 rows but it can grow and I want to be able to use the loop only on the rows that have data in them. how can I change it to go from A1 to the last not empty row?

See stackoverflow.com/q/114493/11683

GSerg
– GSerg

2013-07-27 10:29:12 +00:00
Commented Jul 27, 2013 at 10:29 — GSerg
– GSerg, Commented Jul 27, 2013 at 10:29
Within the loop, your .Range("A1") is redundant.

Tim Williams
– Tim Williams

2013-07-27 16:22:11 +00:00
Commented Jul 27, 2013 at 16:22 — Tim Williams
– Tim Williams, Commented Jul 27, 2013 at 16:22
stackoverflow.com/questions/15770574/…

Zhenya
– Zhenya

2013-07-27 22:10:24 +00:00
Commented Jul 27, 2013 at 22:10 — Zhenya
– Zhenya, Commented Jul 27, 2013 at 22:10

Hauke P. · Accepted Answer · 2013-07-27 10:28:49Z

2

Dim RowIndex As Long
RowIndex = 1

Dim c

While Not IsEmpty(Worksheets("sheet1").Cells(RowIndex, 1))
    Set c = Worksheets("sheet1").Cells(RowIndex, 1)
    c.Offset(0, 1).Range("A1").Value = Right((Left(c, 13)), 7)
    RowIndex = RowIndex + 1
Wend

answered Jul 27, 2013 at 10:28

Hauke P.

2,8531 gold badge23 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

matzone · Accepted Answer · 2013-07-27 10:30:10Z

1

You may try this ...

Dim r As Range

Set r = Range("A65536").End(xlup)

For Each c In Worksheets("sheet1").Range("A1:" & r.Address).Cells
   c.Offset(0, 1).Range("A1").Value = Right((Left(c, 13)), 7)
Next

answered Jul 27, 2013 at 10:30

matzone

5,7293 gold badges19 silver badges20 bronze badges

1 Comment

brettdj Over a year ago

Should be Set r = Cells(Rows.Count,"A").End(xlup) to handle xl07 and onwards - the current formula was appropriate for xl03 and earlier but many more rows now.

Collectives™ on Stack Overflow

For loop in excel VBA

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related