Loop based on number of rows

L

Legacy 53668

Guest
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

Active Member
Joined
Nov 17, 2004
Messages
262
Try using this.

Code:
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

Active Member
Joined
Nov 17, 2004
Messages
262
Sorry forgot to mention that in your code you should put:
Code:
Sheets("sheet1").range("a" & CheckRow) = "TEST"
This will put TEST in each cell from a1 to the end cell in the a column.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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. :eek:

Code:
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
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top