Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a range from bottom to top
I am retrieving a table from a webpage and the data is sorted in
reverse order (by date). It is a dynamic range but I compute the last row of the range before processing. I am currently resorting the data so I can process it by date (ascending). My question is how can modify my current code to read from the bottom of the range to the top (to eliminate the need for the sort)? Thanks for any ideas/ suggestions. -pb Dim lngLast As Long Dim rngCell As Range ' find the last row lngLast = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row If (lngLast < 4) Then ' first row (if data exists) is always in row 4 Exit Sub End If ' sort the WebData Range("A4:E" & lngLast).Select Selection.Sort _ Key1:=Range("A4"), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' Loop through the WebData For Each rngCell In Worksheets("Sheet1").Range("A4:A" & lngLast) ' process table here Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a range from bottom to top
cubbybear3,
I would add a long data type variable and change your for loop: Dim lngIndex as long 'For Each rngCell In Worksheets("Sheet1").Range("A4:A" & lngLast) 'Change your previous loop above to this: For lngIndex = lngLast to 4 step -1 Next lngIndex And then inside the loop, wherever you need to process rngCell, change it to "Cells(lngIndex,1)" (you might have to add "Worksheets("Sheet1")." to the "Cells()" as well) HTH, conan "cubbybear3" wrote in message ... I am retrieving a table from a webpage and the data is sorted in reverse order (by date). It is a dynamic range but I compute the last row of the range before processing. I am currently resorting the data so I can process it by date (ascending). My question is how can modify my current code to read from the bottom of the range to the top (to eliminate the need for the sort)? Thanks for any ideas/ suggestions. -pb Dim lngLast As Long Dim rngCell As Range ' find the last row lngLast = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row If (lngLast < 4) Then ' first row (if data exists) is always in row 4 Exit Sub End If ' sort the WebData Range("A4:E" & lngLast).Select Selection.Sort _ Key1:=Range("A4"), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' Loop through the WebData For Each rngCell In Worksheets("Sheet1").Range("A4:A" & lngLast) ' process table here Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a range from bottom to top
Muchas gracias Conan & Don!
(Conan, thanks for answering what would have been my next question: how to access rngCell and any offsets inside the loop.) -pb |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Process a range from bottom to top
Glad to help!!! :-D
"cubbybear3" wrote in message ... Muchas gracias Conan & Don! (Conan, thanks for answering what would have been my next question: how to access rngCell and any offsets inside the loop.) -pb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding in row at bottom of range | Excel Programming | |||
starting from bottom of range instead of top | Excel Programming | |||
How to apply OFFSET as the range in a basic 'Copy' process... | Excel Discussion (Misc queries) | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming |