CODEWITHSUNDEEP
vbaexcel
user1040563
user1040563

Reputation: 5261

For loop in excel VBA

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?

Upvotes: 3

Views: 3746

Answers (2)

matzone
matzone

Reputation: 5719

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

Upvotes: 1

Hauke P.
Hauke P.

Reputation: 2823

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

Upvotes: 2

Related Questions