Selecting and then sorting a range

[Sharkbait]

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]

Can you pick out a column that always has data in it if the row is used?

I'm gonna use column A.

dim LastRow as long
dim myRng as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("A26:O" & lastrow)
end with

with myrng
.sort key1:=.columns(1), order1:=xlascending, header:=....
end with

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

[Excelenator]

Why not just select Cell A26 and use

Selection.CurrentRegion.Select

??