• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Oplopend sorteren in filter

Status
Niet open voor verdere reacties.
D

Depant

Verenigingslid
Lid geworden
5 aug 2015
Berichten
238
Hallo Allemaal

Ik gebruik onderstaand filter in 10 varianten. (bedankt Harry, het werkt als een tierelier)
Is er een mogelijk om dit uit te breiden zodat ze ook oplopend gesorteerd worden?
( Nu worden ze gesorteerd volgens filter.)

Bij voorbaat hartelijk dank

Henk Harbers

With Sheets("selecteren")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1nir").Range("A2")


Alvast bedankt
 
Laatst bewerkt:
Zoiets?
Code: 
Sub hsv()
With Sheets("selecteren").Cells(1).CurrentRegion
   .AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
   .AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
   .Copy Sheets("1nir").Cells(1)
   .AutoFilter
End With
 With Sheets("1nir")
    .Cells(1).CurrentRegion.Sort .Cells(1), , , , , , , 1
 End With
End Sub
 
Is het niet logischer om eerst de gegevens te sorteren en vervolgens te filteren?
 
Laatst bewerkt:
Misschien mogen de basisgegevens niet gesorteerd worden.
 
sorteren

Goedenavond.

In het blad (selecteren) waar ik de gegeven ophaal staat alles gesorteerd.
Maar door het filter worden uiteraard diegene het eerst gesorteerd, die aan het 1ste criterium voldoen. enz
Vervolgens moet ik van 12 tabbladen die nummers weer sorteren.
Misschien is het makkelijk om het naderhand te doen.
Ik gebruik dat filter 16 keer in 1 betand.



With Sheets("selecteren")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1nir").Range("A2")


.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!b2:b11="","~",criteria!b2:b11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("2nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!c2:c13="","~",criteria!c2:c13)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("3nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!d2:d23="","~",criteria!d2:d23)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("4nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!e2:e8="","~",criteria!e2:e8)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("6nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!f2:f3="","~",criteria!f2:f3)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("8nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!g2:g9="","~",criteria!g2:g9)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("12nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!h2:h114="","~",criteria!h2:h114)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("13nir").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!i2:i11="","~",criteria!i2:i11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("16nir").Range("A2")




.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!a2:a10="","~",criteria!a2:a10)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("1lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!b2:b11="","~",criteria!b2:b11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("2lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!c2:c13="","~",criteria!c2:c13)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("3lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!d2:d23="","~",criteria!d2:d23)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("4lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!e2:e8="","~",criteria!e2:e8)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("6lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!f2:f3="","~",criteria!f2:f3)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("8lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!g2:g9="","~",criteria!g2:g9)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("12lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!h2:h114="","~",criteria!h2:h114)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("13lab").Range("A2")

.Cells(1).CurrentRegion.AutoFilter 5, Filter(Application.Transpose([if(criteria!i2:i11="","~",criteria!i2:i11)]), "~", False), 7
.Cells(1).CurrentRegion.AutoFilter 1, Filter(Application.Transpose([if(criteria!n2:n5="","~",criteria!n2:n5)]), "~", False), 7
.AutoFilter.Range.Offset(1).Columns(2).Copy Sheets("16lab").Range("A2")


Selection.AutoFilter


End With
End Sub
 
Graag gebruik maken van codetags @Depant.

Zet onderstaande code eens in ThisWorkbook.
Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Sh.Cells(1).CurrentRegion.Sort sh.Cells(1), , , , , , , 1
End Sub

Of:
Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
 If lcase(Sh.Name) <> "selecteren" Then Sh.Cells(1).CurrentRegion.Sort Sh.Cells(1), , , , , , , 1
End Sub
 
Laatst bewerkt:
sorteren

Harry
Allereerst weer bedankt voor je hulp.

Ik heb 2 vragen
1 Ik wil nog een tabblad <> dat heet "criteria"
2.Waar moet ik die code invoegen??

Ik durf eigenlijk niks meer te vragen.
Maar het eindresultaat is prachtig
Ik kan hiermee de resultaten van klanten van over de wereld in kaart brengen
Dat zijn dan kwaliteitszaken met prachtige grafieken. Helemaal top!!


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
If lcase(Sh.Name) <> "selecteren" Then Sh.Cells(1).CurrentRegion.Sort Sh.Cells(1), , , , , , , 1
End Sub
 
Mooi te vernemen Depant.
Code: 
If lcase(Sh.Name) <> "selecteren" and lcase(sh.name) <> "criteria" Then Sh.Cells(1).CurrentRegion.Sort Sh.Cells(1), , , , , , , 1

Gewoon vragen stellen, maar dan wel graag met codetags
Code: 
 .....hier de code....
.

Code in Thisworkbook plaatsen.
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan