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