Auto fill based on cRows calculation
 

The statement you have searches from the bottom, up, for the last used cell in the column. If
you have two blocks of data, and you want to work only with the first one, you can try

Cells(1, 7).End(xlDown).Row

which will return the row just above the first blank cell in column G. Note that to find the
last row in the block this way, you have to use a column that has no embedded blank cells.

Another possibility is

cRows = Range("A1").CurrentRegion.Rows.Count


On Fri, 8 Aug 2003 18:37:36 -0500, "Bruce Roberson" wrote:

If I have have 166 consecutive rows in column G that have information, and
then after that, there is a gap of lets say 192 rows before some unrelated
information is in column G there, then the formula for cRows yields 358,
when it should only yield the value of 166.

Dim Crows as Long
cRows = Cells(Rows.Count, "G").End(xlup).Row

I am using this kind of thing to work through the next few rows in which
I'm doing some copying of other row values based on the contiguous values in
column G as described above.

Range("a10:F10").AutoFill Destination:=Range("A10:F10", Cells(cRows, "A"))
Range("A10:F10", Cells(cRows, "A")).Copy
Range("A10:F10", Cells(cRows, "A")).PasteSpecial Paste:=xlValues

Auto fill based on cRows calculation
 

"Myrna Larson" wrote in message
...
The statement you have searches from the bottom, up, for the last used
cell in the column. If
you have two blocks of data, and you want to work only with the first one,
you can try

Cells(1, 7).End(xlDown).Row


Actually, I had to change the Cells(1,7) to (10,7) since 10 was my first
populated row, but I see what you mean about the up and down etc, so this
will work for me I think on this particular excercise.

Thanks