Help with a Dynamic range

[thesquirrel]

I need to create a dynamic range, but I am coming up short on ideas on
how to accomplish this.

I have a program that finds the last row with data and enters new data
on the row below that, after the new data is entered I want to sort it
by column D (the date).

My range starts in cell A20 and extends as far as the data exists on
teh sheet. I don't expect to really need more than 1000 lines (an
eventual 2.7 years of data), but I didn't want to put any artificial
limits on the program.

How can I create a dynamic range based on teh last row with data in it?

Here is what I have so far, but I don't think I am going in the correct
direction. There has to be an easier way to do this.

Dim SortRange as Range
Set SortRange = Sheets("RawData").Range("A20:AB" & LastRow)
Range(SortRange).Sort Key1:=Range("D20"), Order1:=xlAscending

Please help!

 

[Don Guillett]

lastrow=cells(rows.count,"a").end(xlup).row

 

[thesquirrel]

The LastRow in my code already has the last row in it. When I run the
above code I get an Error 1004 "Application-Defined or Object-Defined
error"

I have also tried it like this:

'find the last cell in the range
Dim SortRange As Range
Set SortRange = Sheets("RawData").Range("A20:AB" & LastRow)

'now do the sort
Sheets("RawData").Range(SortRange).Select
Selection.Sort Key1:=Range("D20"), Order1:=xlAscending

and it errors on this line with the same 1004 error:
Sheets("RawData").Range(SortRange).Select

 

[Dave Peterson]

You can only select a range if that worksheet is active.

You could add:
worksheets("rawdata").select
before you try selecting, but you don't have to select the data to sort it.

Dim SortRange as Range
Dim Lastrow as long
with worksheets("RawData")
lastrow = .cells(.rows.count,"A").end(xlup).row 'or something!
Set SortRange = .Range("A20:AB" & LastRow)
end with
'sortrange is already a range, so don't use range(SortRange)
with sortrange
.sort key1:=.columns(4), order1:=xlascending
end with

You can usually work with stuff without selecting it. I think you'll find that
your code is easier to understand without the .selects (and .activates). And
it'll even run a bit quicker.

ps. I'd add "header:=" to the .sort statement. I don't let excel guess if I
know what it should be.

 

[Bob Phillips]

SortRange is a range object, so just use

SortRange.Sort Key1:=SortRange.Cells(1,1).Offset(0,4), Order1:=xlAscending


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)