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

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

Process a range from bottom to top
 

lngLast = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
for i =lnglast to 4 step-1
do your thing
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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