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?

Answer 1

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

Answer 2

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