Sort Contents of Moving Range

[mcwhirt3]

I haven't been able to pull different topics together to find a
concrete answer for this. I have a project list i am making, and i have
a macro to insert a line underneath the header(which is a range), so
users basically just keep inserting rows on and on. The sheet allows
for 2 different "sort criteria". I have the sort macro in place, and
currently have users highlighting the content and hitting the button to
sort their entries. What i would like to do is have excel go from the
header range, look down until it finds and empty cell, then look right
until it finds an empty cell, and automatically select the cells with
contents so that then it can sort itself with one click and no need to
highlight. I know this is possible i just don;t know how to incorporate
excels look for blank logic. Thanks for any help.

 

[Tom Ogilvy]

Range("B10").CurrentRegion

Change B10 to the top left corner of your data.

To test it do

Range("B10").CurrentRegion.Select

does that get what you want?

If you want to include headers and there is a blank row below the header
(headers in row 1).

set rng = Range(Range("A1"), Range("A3").CurrentRegion")
rng.Select

 

[Guest]

hi,
i'm a little confused.
go where at in the header? Cell A1?
does the range contain blanks? if i when down to an empty
cell, would that be the last row +1?
the empty cell to the right. is that the end of yoru data?
It reads like your are selecting all your data?
correct?
HDR

 

[mcwhirt3]

i haven't tried any of this, looks to be the wrong idea. I can't use a
cell refrence because the rows are constantly changing because things
are being inserted. The range follows the moves therefore i wanted to
strat at the top left corner of the range and find all the data in it.
there are no blanks within the range itself.

 

[Tom Ogilvy]

I doubt the header range moves and if it is cell A1, then

set rng = Range("A1").CurrentRegion

Then to test
rng.Select


If you fell more comfortable doing it the way you described

Set rng = Range(Range("A1"),Range("A1").End(xldown).End(xltoRight))
to test
rng.Select