Run piece of code for each line of data

I have a spreadsheet that will have a varying number of rows of data. I need
to write a macro that will (for each row of data) run my code until there are
no more rows of data.

Also, my data rows do not start at the top of the sheet, the rows start at
row 5 so I guess I will need some type of offset or something like that.

While there's rows of data

- my code here -

End when there's no more data

Any help would be greatly appreciated.

This suggestion isnt using "code" but just some native-excel functions...

You might be able to use the =countif() function to generate a cell
reference array that exactly fits your data as it changes.

If your data is populated from A5 through F105 for example... you would need
a cell reference stating "$A$5:$F$105". Use Countif(A:A,"> ") to return 100
(assuming that no headings are in row 1-4 but a simple offset can adjust for
them) Then make cell AA1 ="$A$5:$F$"&Countif(A:A,"> ")+5
Then reference this cell using "INDIRECT(AA1)" in any formulas that need a
dynamically changing cell-reference for your array of data.

NOTE: the "> " is 2 characters, a greater than and a space, which will count
the entries that are greater than a space.

Hope this helps (i'm still learning the "code" part too)

"Diane Alsing" wrote:

> I have a spreadsheet that will have a varying number of rows of data. I need
> to write a macro that will (for each row of data) run my code until there are
> no more rows of data.
>
> Also, my data rows do not start at the top of the sheet, the rows start at
> row 5 so I guess I will need some type of offset or something like that.
>
> While there's rows of data
>
> - my code here -
>
> End when there's no more data
>
> Any help would be greatly appreciated.

Assuming the data is continuious starting in cell A5 through A?. Here is some
fairly easy code to understand. It is not necessarily the most efficient not
the most fool proof, but it will work and should get you started.

sheets("Sheet1").Range("A5").Select

do while activecell.value <> ""
msgbox activecell.value
loop

Give this a try and let me know if you wnat some more help...

HTH

"Diane Alsing" wrote:

> I have a spreadsheet that will have a varying number of rows of data. I need
> to write a macro that will (for each row of data) run my code until there are
> no more rows of data.
>
> Also, my data rows do not start at the top of the sheet, the rows start at
> row 5 so I guess I will need some type of offset or something like that.
>
> While there's rows of data
>
> - my code here -
>
> End when there's no more data
>
> Any help would be greatly appreciated.

sorry in my code I forgot to add a line after the messagebox

activecell.offset(1,0).select

Sorry....

HTH

"Diane Alsing" wrote:

> I have a spreadsheet that will have a varying number of rows of data. I need
> to write a macro that will (for each row of data) run my code until there are
> no more rows of data.
>
> Also, my data rows do not start at the top of the sheet, the rows start at
> row 5 so I guess I will need some type of offset or something like that.
>
> While there's rows of data
>
> - my code here -
>
> End when there's no more data
>
> Any help would be greatly appreciated.

Try using CurrentRegion

Dim MyRng as Range
Dim cell as Range
Dim i as Integer

' This sets MyRng to the entire region or area of cells that contain data
and "touch" cell A5
Set MyRng = Range("A5").CurrentRegion

For Each cell In MyRng.Columns(1).Cells
cell.Select
For i = 0 to MyRng.Columns.Count
MsgBox ActiveCell.OffSet(0, i - 1).Value
Next i
Next cell

This will cycle through all cells in your range, where MyRng represents the
entire range of rows and columns. The outer loop just cycles through the
first cell in each row, and the inner loop cycles through all cells in the
row. The loop

For Each cell In MyRng.Cells
MsgBox cell.Value
Next cell

will cycle through all cells in the entire range also, but distinguishing
between rows is more difficult. The example I listed has a flaw in that if
one of the rows has no data in it whatsoever, then the CurrentRegion method
will fail.

There are lots of ways to loop through a range. Think about setting a range
variable to the entire range of data, and look at the Row.Count,
Column.Count and Offset properties and methods also.

HTH.

-gk-




"Diane Alsing" <DianeAlsing@discussions.microsoft.com> wrote in message
news:E1085559-3D86-4310-BD9D-68DBCCF861CE@microsoft.com...
>I have a spreadsheet that will have a varying number of rows of data. I
>need
> to write a macro that will (for each row of data) run my code until there
> are
> no more rows of data.
>
> Also, my data rows do not start at the top of the sheet, the rows start at
> row 5 so I guess I will need some type of offset or something like that.
>
> While there's rows of data
>
> - my code here -
>
> End when there's no more data
>
> Any help would be greatly appreciated.

I ended up combining all these options (thanks for the help!) I used the
counta function to find out how many rows I had, then used the
activesheet.Cells in a for next loop -

"Jim Thomlinson" wrote:

> Assuming the data is continuious starting in cell A5 through A?. Here is some
> fairly easy code to understand. It is not necessarily the most efficient not
> the most fool proof, but it will work and should get you started.
>
> sheets("Sheet1").Range("A5").Select
>
> do while activecell.value <> ""
> msgbox activecell.value
> loop
>
> Give this a try and let me know if you wnat some more help...
>
> HTH
>
> "Diane Alsing" wrote:
>
> > I have a spreadsheet that will have a varying number of rows of data. I need
> > to write a macro that will (for each row of data) run my code until there are
> > no more rows of data.
> >
> > Also, my data rows do not start at the top of the sheet, the rows start at
> > row 5 so I guess I will need some type of offset or something like that.
> >
> > While there's rows of data
> >
> > - my code here -
> >
> > End when there's no more data
> >
> > Any help would be greatly appreciated.