I have a workbook with the following two Sheets. In Sheet1 there is the data.
| Order | Date | Status | Country |
|-------|---------:|-------:|---------|
| 78 | 19-04-19 | OK | FR |
| 33 | 19-04-19 | OK | GB |
| 198 | 19-04-19 | CL | GB |
| 43 | 19-04-19 | CL | CH |
| 29 | 19-04-19 | CL | CN |
| 12 | 20-04-19 | CL | GB |
| 169 | 20-04-19 | OK | GB |
| 95 | 20-04-19 | OK | GB |
| 54 | 20-04-19 | OK | FR |
| 31 | 20-04-19 | OK | GB |
What I am trying to achieve is to sort 'Country' unique values under the condition that Status=OK. So far, with a bit of help I have managed to write the code for the case that there is no condition.
Sub SortUniqueValues1()
Dim i As Variant
Dim j As Variant
With Sheets("Sheet1")
j = Application.Transpose(.Range("H2", .Range("H" & Rows.Count).End(xlUp)))
End With
With CreateObject("Scripting.Dictionary")
For Each i In j
.Item(i) = i
Next
Cells(3, 1).Resize(.Count) = Application.Transpose(.Keys)
End With
Range("A3:M100000").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlNo
End Sub
I am trying to find out the following:
- How can I integrate into the code the Status Condition?
- Is there any way to create some sort of dropdown menu that will turn on/off conditions?
- How can I convert A3:M100000 to A3 until the last value in column M?
Thank you in advance!
Aucun commentaire:
Enregistrer un commentaire