Select Range With xlDown Doesn’t Work (97 SR2)

Topic

New Reply

I

Reply | Quote

Replies

If column V is guaranteed not to have empty cells, then you can use:

    Range("F3", Range("V1").Offset(Range("V3").End(xlDown).Row - 1, 0)).Select

If there is any chance of an empty cell in V, then the following will always work:

    Range("F3", Range("V1").Offset(Range("V65536").End(xlUp).Row - 1, 0)).Select

Reply | Quote

Legare,
The first code works great but I am trying to understand WHY it works.

I read about the Offset Method and it said it needed an input range, a row offset, and a column offset. So I

Reply | Quote

F3 is the upper left corner of the area you want to select, and has nothing to do with the Offset property. Range(“F3”, specifies the upper left cell of the range that you want to select.

The Range(“V1”) is the “input range” for the Offet property. In other words, that is cell from which Offset will be used to specify the bottom right cell of the range to be selected.

Range(“V3”).End(xlDown).Row will find the row number of the row above the first empty cell in column V after row 3 (in case row 1 or 2 is empty). This minus one is used as the row offset, and the column offset is zero since we are offsetting from column V already. The minus 1 is because offset zero is required to specify the starting cell. So, if V4 is empty, the resulting row number would be 3. Since we are offsetting from V1, a row offset of 0 would be V1, an offset of 1 would be V2, and an offset of 2 would be V3 (which is what we want in this case). Therefore, to get the correct offset we must subtract one from the row number.

Reply | Quote