Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Dynamic range
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Dynamic range
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Dynamic range
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 On Dec 27, 5:20 pm, "Don Guillett" wrote: lastrow=cells(rows.count,"a").end(xlup).row -- Don Guillett SalesAid Software wrote in oglegroups.com... 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!- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Dynamic range
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. " wrote: 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! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Dynamic range
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) wrote in message ps.com... 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 On Dec 27, 5:20 pm, "Don Guillett" wrote: lastrow=cells(rows.count,"a").end(xlup).row -- Don Guillett SalesAid Software wrote in oglegroups.com... 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!- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
copying dynamic range based on cell outside of range | Excel Programming | |||
select dynamic range with dynamic start point | Excel Programming | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming |