Problem using a For loop with Step to read to the end of file

Posted by Stephen on October 05, 2000 9:05 AM

I would like to learn how to make the macro I am using read to the end of the file with out having to put the number of rows in each time. It looks soming like this: For y = 1 To 30886 Step 60
pcount = pcount + 1
For x = 1 To 11
array1(pcount, x) = space(y, x).Value
Next x
Next y

Each time I have to put the number of row(30886) in the file in order for it to loop to the end of file. If anyone could help I would by very thankfull, Thanks.

Posted by Scott H on October 05, 2000 2:40 PM

One way to find the end of the file is to use the command CurrentRegion. However, this only works on files that have continuous data values )no blank spaces in between rows). If your data file is like this, do the following:

Select the first cell in the first column, then run your macro.

In the macro include the following:

Set Sheet = ActiveSheet
varCount = ActiveCell.CurrentRegion.Rows.Count
For y = 1 to varCount Step 60
.
.
.
.

Let me know how it works!

Posted by Stephen on October 06, 2000 10:49 AM

Thanks Scott, it works great. I thank I tried both ActiveCell and CurrentRegion, but not togeather. Thanks again for the help.