Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dynamic Range and sorting
I have a dynamic Range Starting from Cell B9 and the last column is R
It is not necessary that the last cell in the last column to have a value. But the Last row in Column B will have a value. This is the code i used to select the range and sort With Sheets("Unassigned Requests") Range(Range("B9"), Range("B9").End(xlDown)(0)).EntireRow.Select Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range("C9") _ , Order2:=xlAscending, Key3:=Range("E9"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal End With The above code, does not select the last row in the range. Also at the end, it has the rows selected, can i show it de-selected? Thanks in Anticipation Thiaga |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dynamic Range and sorting
It doesn't select the last row because you told it not to with your (0)
This will avoid the selecting so no need to unselect. Note there are periods entered prior to each range reference so it points back to Sheets("Unassigned Requests") With Sheets("Unassigned Requests") .Range(.Range("B9"), .Range("B9") _ .End(xlDown)).Resize(,17).Sort _ Key1:=.Range("B9"), _ Order1:=xlAscending, _ Key2:=.Range("C9"), _ Order2:=xlAscending, _ Key3:=.Range("E9"), _ Order3:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal, DataOption3:=xlSortNormal End With -- Regards, Tom Ogilvy "thiaga" wrote in message oups.com... I have a dynamic Range Starting from Cell B9 and the last column is R It is not necessary that the last cell in the last column to have a value. But the Last row in Column B will have a value. This is the code i used to select the range and sort With Sheets("Unassigned Requests") Range(Range("B9"), Range("B9").End(xlDown)(0)).EntireRow.Select Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range("C9") _ , Order2:=xlAscending, Key3:=Range("E9"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal End With The above code, does not select the last row in the range. Also at the end, it has the rows selected, can i show it de-selected? Thanks in Anticipation Thiaga |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dynamic Range and sorting
Perfect! Thanks Tom..
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dynamic Range and sorting
Perfect! Thanks Tom..
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Selecting a dynamic range | Excel Programming | |||
selecting a dynamic range | Excel Programming | |||
Selecting and copying a dynamic range of cells | Excel Programming | |||
Need help Selecting, Sorting, Autofitting a range | Excel Programming |