Try this
To start with worksheet looks like this
Excel 2016 (Windows) 32 bit

A 
B 
1 
17.695367 
94.882509 
2 
22.604667 
89.515209 
3 
20.978867 
89.262209 
4 
19.682367 
87.809709 
5 
19.957467 
91.277409 
6 
20.025367 
86.121809 
7 
21.344367 
93.437609 
8 
26.171767 
90.014609 
9 
27.427167 
93.532009 
10 
20.042567 
87.880009 
11 
24.227967 
88.523609 
12 
24.139467 
93.652109 
13 
25.026667 
86.809209 
14 
19.714567 
91.068309 
15 
27.213467 
87.960409 
16 
24.800967 
92.572709 
I asked for 10 values to be returned :
Excel 2016 (Windows) 32 bit

A 
B 
C 
D 
E 
1 
21.50117 
91.1147 
21.49797 
91.12 
0.406253 
2 
18.94137 
87.4702 
18.94487 
87.4756 
0.427896 
3 
19.01427 
90.0209 
19.01157 
90.0282 
0.512166 
4 
21.91217 
93.2721 
21.92447 
93.2732 
0.85295 
5 
20.12217 
89.9606 
20.11197 
89.9746 
1.149885 
6 
21.50377 
93.494 
21.48577 
93.4935 
1.244361 
7 
25.13587 
86.2795 
25.13527 
86.2994 
1.245712 
8 
20.11167 
87.9784 
20.10727 
87.9595 
1.263648 
9 
20.23217 
85.3814 
20.22367 
85.3998 
1.329925 
10 
27.59177 
87.1582 
27.59537 
87.1796 
1.334098 
11 





Sheet: Results 190724 10.18 pm 
Test on a COPY of your workbook!
Place the code in a NEW standard module
Code:
'this goes at TOP of module ABOVE all procedures
Option Explicit
Private List() As Variant, Pairs() As Double
Const Limit As Long = 500000
Private PairCount As Long, ResultsCount As Long, Results As Worksheet, Data As Worksheet
'procedures
Sub Strat919()
Dim t As Double: t = Timer
ResultsCount = InputBox("How many items to output ?", "User choice", 100)
Call GeneratePairs
Call GenerateResults
MsgBox Round(Timer  t, 1) & " seconds"
End Sub
Code:
Private Sub GeneratePairs()
Dim a As Long, b As Long, r As Long
Set Data = ActiveSheet
List = Data.Range("A1", Data.Range("A" & Data.Rows.Count).End(xlUp)).Resize(, 2)
PairCount = (UBound(List) ^ 2  UBound(List)) / 2
ReDim Pairs(1 To PairCount, 1 To 5)
'place paired values in array and calculate distance
For a = 1 To UBound(List)
For b = 1 To UBound(List)
If b < a Then
r = r + 1
Pairs(r, 1) = List(a, 1)
Pairs(r, 2) = List(a, 2)
Pairs(r, 3) = List(b, 1)
Pairs(r, 4) = List(b, 2)
Pairs(r, 5) = GetDistance(Pairs(r, 1), Pairs(r, 2), Pairs(r, 3), Pairs(r, 4))
End If
Next b
Next a
End Sub
Private Function GetDistance(ByVal Lat1, ByVal Long1, ByVal Lat2, ByVal Long2)
With WorksheetFunction
GetDistance = 6371 * .Acos(Cos(.Radians(90  Lat1)) * Cos(.Radians(90  Lat2)) + Sin(.Radians(90  Lat1)) * Sin(.Radians(90  Lat2)) * Cos(.Radians(Long1  Long2))) / 1.609
End With
End Function
Code:
Private Sub GenerateResults()
Dim r2 As Long, r1 As Long, c As Long, LimitCount As Long, Remainder As Long
'insert new worksheet
Set Results = Sheets.Add(before:=Sheets(1))
Results.Name = "Results " & Format(Now, "yymmdd h.mm am/pm")
'how many times to move subsets of values?, how many items in last subset?
LimitCount = WorksheetFunction.RoundDown(PairCount / Limit, 0)
Remainder = PairCount Mod Limit
'move each subset in sequence
r1 = 1
For c = 1 To LimitCount
r2 = r2 + Limit
Call MoveSubset(r1, r2)
r1 = r1 + Limit
Next c
If Remainder > 0 Then
r2 = r2 + Remainder
Call MoveSubset(r1, r2)
End If
End Sub
Private Sub MoveSubset(firstItem As Long, lastItem As Long)
Application.ScreenUpdating = False
Dim rTemp As Long, r As Long, c As Long, tempArr()
ReDim tempArr(1 To lastItem  firstItem + 1, 1 To 5)
'move to temp array
For r = firstItem To lastItem
rTemp = rTemp + 1
For c = 1 To 5
tempArr(rTemp, c) = Pairs(r, c)
Next c
Next r
'write to worksheet, sort and clear rows not required
Results.Cells(Results.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(tempArr), 5) = tempArr
Results.Range("A:E").Sort Key1:=Results.Range("E1"), Order1:=xlAscending, Header:=xlNo
Results.Range("A1").Offset(ResultsCount).Resize(Limit, 5).ClearContents
End Sub
VBA actions
 the user is asked how many items to return (let's assume user wants 200 )
 coordinates in columns A and B of active sheet are written to a 2 column array
 possible pairings are determined, distances calculated and values written to a 5 column array
 results are written to a new worksheet
 a restricted no of items are written to the worksheet, sorted (ascending), and the first 200 rows retained
 the restriction is the value of constant Limit
 then next batch of items are witten to the sheet (now contains Limit + 200), sorted and the first 200 rows retained
etc until all values dealt with
Trapping errors etc
 have not had time to be sophisticated
 what could cause VBA problems with your data ( text instead of values, no values , values that break the formula etc)
 can incorporate error checking later
User Options
 do you always want the shortest distances ?
 what else is required ?
Let me know how you get on
Like this thread? Share it with others