Loop based on number of rows

[Legacy 53668]

I've created a macro that manipulates data in the first row of my shirt, including copying that data to another file. I want to run that macro again for every row in the worksheet. However, the number of rows can be 2 one day and 100 the next. How can I tell the macro to continuously run for the amount of data I have, which happens to change every day?

 

[StevenD]

Try using this.

dim MyEndRow as Integer
dim CheckRow as Integer
'Find End Row of Column A
MyEndRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row
'Carry Out code for each row
For CheckRow = 1 to MyEndRow
'---Your Code For That Row HERE---
Next CheckRow

Change the Sheet1 to your sheet name.

HTH

 

[StevenD]

Sorry forgot to mention that in your code you should put:

Sheets("sheet1").range("a" & CheckRow) = "TEST"

This will put TEST in each cell from a1 to the end cell in the a column.

 

[Nimrod]

DEMO of 3 methods of finding the bottom row used on a sheet.
Please make sure that your activesheet is blank , before running the demo.

Public Sub demo()
' place some values in sheet
For rw = 5 To 40 Step 2
    col = col + 1
    If col > 4 Then col = 1
    
    Cells(rw, col).Value = rw
Next rw

' NOW SHOW DIFFERENT TECHNIQUES FOR FIND BOTTOM

    'ONLY GIVE TRUE BOTTOM IF NO EMPTY ROWS AT TOP
    MsgBox ActiveSheet.UsedRange.Rows.Count
    
    ' Must specify which row to look in
    MsgBox Cells(65536, 1).End(xlUp).Row
    
   
   ' Will find bottom row of any column
    MsgBox Range("a1:iv65536").Find("*", SearchDirection:=xlPrevious)
    
    
End Sub