J'ai ajouté
UpdateCountries
Sub UpdateCountries()
Dim dico1 As Object, dico6 As Object
tbl = Sheets("Travel Expenses Detail").Range("B4").CurrentRegion
Set dico1 = CreateObject("Scripting.Dictionary")
Set dico6 = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tbl)
dico1(tbl(i, 1)) = ""
dico6(tbl(i, 6)) = ""
Next
With Sheets("Countries")
If Not .ListObjects(1).DataBodyRange Is Nothing Then .ListObjects(1).DataBodyRange.Delete
country = dico6.keys
QuickSort country
.Range("A4").Resize(UBound(country), 1) = Application.Transpose(country)
If Not .ListObjects(2).DataBodyRange Is Nothing Then .ListObjects(2).DataBodyRange.Delete
country = dico1.keys
QuickSort country
.Range("C4").Resize(UBound(country), 1) = Application.Transpose(country)
End With
End Sub
Public Sub QuickSort(vArray As Variant, _
Optional ByVal inLow As Long = -1, _
Optional ByVal inHi As Long = -1)
Dim pivot As Variant
Dim tmpSwap As Variant
Dim tmpLow As Long
Dim tmpHi As Long
inLow = IIf(inLow = -1, LBound(vArray), inLow)
inHi = IIf(inHi = -1, UBound(vArray), inHi)
tmpLow = inLow
tmpHi = inHi
pivot = vArray((inLow + inHi) \ 2)
While (tmpLow <= tmpHi)
While (vArray(tmpLow) < pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Wend
While (pivot < vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Wend
If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
Wend
If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
End Sub
et ajouté après le rafraîchissement du TCD