Hey, I had a problem that maybe someone can help me with.

I'm trying to sort a range in a macro. The columns that compose the
range never change, but the rows will. (i.e. columns a:o, but rows may
vary from 26:400 or 26:40)

I was trying to use range(selection, selection.end(xlDown)) and
range(selection, selection.end(xlToRight))

The selection.end(xlDown) works fine, but I have a partially blank
column in the middle of my column range. It has data for some jobs, but
not all. When I do selection.end(xlTo Right), it stops at this blank.
There is however, data I need sorted on the right of this column.

Is there a way to select the same columns every time, but check for the
end of data when adding rows to the selection?

I appreciate any help.

Range("A1", "O" & Range("A1").End(xlDown).Row)

HTH
Die_Another_Day
Sharkbait wrote:
Hey, I had a problem that maybe someone can help me with.

I'm trying to sort a range in a macro. The columns that compose the
range never change, but the rows will. (i.e. columns a:o, but rows may
vary from 26:400 or 26:40)

I was trying to use range(selection, selection.end(xlDown)) and
range(selection, selection.end(xlToRight))

The selection.end(xlDown) works fine, but I have a partially blank
column in the middle of my column range. It has data for some jobs, but
not all. When I do selection.end(xlTo Right), it stops at this blank.
There is however, data I need sorted on the right of this column.

Is there a way to select the same columns every time, but check for the
end of data when adding rows to the selection?

I appreciate any help.

When trying to find the very last populated cell you are best to come up from
the bottom than to move down from the top as it will stop at the first blank.
This assumes that there is at least one column that is always populated. If
not then we need to get a bit more fancy than this...

dim rngToSort as range

set rngToSort = range(range("O1"), cells(rows.count, "A").end(xlup))

'rngtosort.sort ...
rngToSort.Select
--
HTH...

Jim Thomlinson


"Sharkbait" wrote:

Hey, I had a problem that maybe someone can help me with.

I'm trying to sort a range in a macro. The columns that compose the
range never change, but the rows will. (i.e. columns a:o, but rows may
vary from 26:400 or 26:40)

I was trying to use range(selection, selection.end(xlDown)) and
range(selection, selection.end(xlToRight))

The selection.end(xlDown) works fine, but I have a partially blank
column in the middle of my column range. It has data for some jobs, but
not all. When I do selection.end(xlTo Right), it stops at this blank.
There is however, data I need sorted on the right of this column.

Is there a way to select the same columns every time, but check for the
end of data when adding rows to the selection?

I appreciate any help.

You have other replies at your other posts.

Sharkbait wrote:

Hey, I had a problem that maybe someone can help me with.

I'm trying to sort a range in a macro. The columns that compose the
range never change, but the rows will. (i.e. columns a:o, but rows may
vary from 26:400 or 26:40)

I was trying to use range(selection, selection.end(xlDown)) and
range(selection, selection.end(xlToRight))

The selection.end(xlDown) works fine, but I have a partially blank
column in the middle of my column range. It has data for some jobs, but
not all. When I do selection.end(xlTo Right), it stops at this blank.
There is however, data I need sorted on the right of this column.

Is there a way to select the same columns every time, but check for the
end of data when adding rows to the selection?

I appreciate any help.

--

Dave Peterson