Arrays, Ranges and unknown row count

[yellowc]

I've created a macro that takes a list of addresses and returns the distance. The problem I'm having is that the macro is static, but I don't know how many rows of addresses I'm going to have. I've made it so that the data is returned in an array but once again when populating the column for distance the range is statically set. How can I make so that I can put in any number of addresses and loop through quickly?

Code below

Public Sub getdirections()


Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String, c As Range, rng As Range, xmlresponse As New DOMDocument60
Dim myarray() As Variant, x As Long, d As Range


Set rng = Range("c2:c26")




'single substitution to get one location will work on looping in a bit
For Each c In rng
    myurl = "http://www.mapquestapi.com/directions/v2/route?key=key & c & "&outFormat=xml&ambiguities=ignore&routeType=shortest"
    'Debug.Print myurl
    'sending the request
      xmlhttp.Open "GET", myurl, False
      xmlhttp.Send
      xmlresponse.LoadXML (xmlhttp.ResponseText)
      'once I got XML returns instead of JSON returns all started to work properly
     Set stats = xmlresponse.SelectNodes("//response/route/distance")
     'Sheets(1).Range("e2").Value = stats(0).Text
     ReDim Preserve myarray(x)
     myarray(x) = stats(0).Text
     x = x + 1
    'Range("d2 & rng").Value = stats(0).Text
     If c = "" Then
    Exit For
    End If
Next




For x = LBound(myarray) To UBound(myarray)
    Debug.Print myarray(x)
 Next x


Range("d2:d26").Value = Application.WorksheetFunction.Transpose(myarray)




End Sub

 

[Roderick_E]

Does any of this help?

firstrow = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

 

[yellowc]

Does any of this help?

firstrow = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Yes I can get the count of how many rows but I'm sorta lost as how to implement it in the for each loop I have going on.

count = 1
for each c in rng 
do
"blah blah blah"
count = count + 1
until count == lastrow ???

and then later for transposing the function

range(???).value = Application.WorksheetFunction.Transpose(myarray) ???

 

[Roderick_E]

Maybe more specifically

lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
rngstr = "c2:c" & lastrow
Set rng = Range(rngstr)

 

[yellowc]

OOOOO, that makes so much sense. And it worked perfectly. I took what you gave me and was able to get the array part to function as well. AWESOME. Now are there any caveats for this? Like it I have a cell that I wrote in and deleted, do I have to save first before getting the correct row count? Appreciate it so much

 

[Roderick_E]

OOOOO, that makes so much sense. And it worked perfectly. I took what you gave me and was able to get the array part to function as well. AWESOME. Now are there any caveats for this? Like it I have a cell that I wrote in and deleted, do I have to save first before getting the correct row count? Appreciate it so much

It counts to the lastrow at the moment of the running. You don't have to save before, it counts the cells at the macro run. Glad it worked.