Selecting and then sorting a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sharkbait - 28 Jul 2006 20:59 GMT

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.

Reply to this Message

Dave Peterson - 28 Jul 2006 21:52 GMT

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

> Hey, I had a problem that maybe someone can help me with.
>
[quoted text clipped - 14 lines]
>
> I appreciate any help.

Signature

Dave Peterson

Reply to this Message

Excelenator - 28 Jul 2006 21:58 GMT

Why not just select Cell A26 and use

Selection.CurrentRegion.Select

?

--
Excelenato

Reply to this Message