Variabel filter in VBA

[anton44]

Afhankelijk van de inhoud van een cel de keuze maken uit 2 filtercriteria.
Als concept van mijn kant bijgaande code die ik nog niet werkbaar heb gekregen.

Sub RB104_Filter() 'mutaties voor ingestelde datum ("Variabelen" $G$3 cq $G$4 verwijderen)
10
15    Application.ScreenUpdating = False
20
25    With Sheets("Variabelen")
30        TempnameA = .Range("D9") '= NL14ABCD012345
35        TempnameB = .Range("E9") '= NL03ABCD016789
40      End With
45
50    With Sheets("importRB")
55    If Range("A8") = TempnameA Then
60    With Sheets("importRB").Cells(7, 1).CurrentRegion
65       .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(3, 7), "m-d-yyyy")
70       .Offset(1).EntireRow.Delete
75       .AutoFilter
80    End If
85
90    With Sheets("importRB")
95    If Range("A8") = TempnameB Then
100      .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(4, 7), "m-d-yyyy")
105      .Offset(1).EntireRow.Delete
110      .AutoFilter
115
120     End If
125     End With
130
135    Application.ScreenUpdating = True
140
145   ' Call RB105_Formules_kopiëren
150
End Sub

Bijgaand een voorbeeldbestand.
In tab "Voorbeeldregels" staan regels voor beide situaties die in het blad ImportRB vanaf regel 8 geplakt kunnen worden om te testen.

 

[cow18]

goed opletten op dat voorlooppuntje bij Range("A8") en de With ... End With constructies

Sub RB104_Filter()                               'mutaties voor ingestelde datum (variabelen $G$3 cq $G$4 verwijderen)

'Application.ScreenUpdating = False

   With Sheets("Variabelen")
      TempnameA = .Range("D9")                   '= NL14ABCD012345
      TempnameB = .Range("E9")                   '= NL03ABCD016789
   End With

   With Sheets("importRB")
      Set c = .Range("A8").CurrentRegion         'vanaf rij 8 tot rij ... aangezien rij 7 leeg is (dat is toch altijd zo !!!!!!)
      Set c1 = c.Offset(-1).Resize(c.Rows.Count + 1, 9)   'met deze toch rij 7 meenemen en 9 kolommen

      Select Case c.Cells(1).Value               '
         Case TempnameA
            With c1
               .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(3, 7), "m-d-yyyy")
               '.Offset(1).EntireRow.Delete       'tijdelijk uitgeschakeld voor test
               .AutoFilter
            End With

         Case TempnameB
            With c1
               .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(4, 7), "m-d-yyyy")
               .Offset(1).EntireRow.Delete
               .AutoFilter
            End With
      End Select
   End With

   Application.ScreenUpdating = True

   ' Call RB105_Formules_kopiëren

End Sub

 

[anton44]

@cow18. Met dank voor de snelle en goed werkend oplossing. :thumb:
Een dergelijk script met case enz ben ik niet eerder tegengekomen. Alweer iets bijgeleerd.

 

[anton44]

@cow18
Na implementeren in het origineel toch 2 probleempjes met onderstaande regels

   With Sheets("importRB")
      Set c = .Range("A8").CurrentRegion         'vanaf rij 8 tot rij ... aangezien rij 7 leeg is (dat is toch altijd zo !!!!!!)
      Set c1 = c.Offset(-1).Resize(c.Rows.Count + 1, 9)    'met deze toch rij 7 meenemen en 9 kolommen

1e Rij 7 is wel gevuld maar mag niet meegaan in de filtering. Mijn pogingen om voor c.Offset 0 of +1 te gebruiken hebben niet het gewenste effect, evenals de +1 te wijzigen in +2
2e Het origineel telt momenteel 37 kolommen en zou nog kunnen groeien. Waarde 9 wijzigen in b.v. 40 ?? Of hele rij selecteren ?

 

[cow18]

2. aangezien je filtert op de 4e kolom en daarna de ganse rij verwijdert, maakt die 9 of die 40 niet veel uit, zolang die maar >=4
1. tja, eventjes snel uit de pols, c verdwijnt en c1 wordt nu het bereik van A8 tot de laatst gebruikte A-cel, minstens 4 kolommen breed

set c1=.Range("A8:A" & .range("A" & rows.count).end(xlup).row).resize(,[COLOR="#FF0000"][SIZE=4]4[/SIZE][/COLOR])

maar door op de volgende regel die ".offset(1)" zal de 8e rij nooit verwijderd worden, zelfs al voldoet die aan de voorwaarden, omdat je die aanziet als koprij.
Ik zou dan toch een rij 7 als een soort valse koprij maken en de macro zijn werk laten doen. Dat doe je door die "A8" te vervangen door A7.

 

[anton44]

Bedankt.
Zodra cel A7 gevuld wordt werkt de macro niet ongeacht de waarde van de offset.

 

[cow18]

je werkt toch niet met samengevoegde cellen in rij 7 ?!?

Sub RB104_Filter()                               'mutaties voor ingestelde datum (variabelen $G$3 cq $G$4 verwijderen)

'Application.ScreenUpdating = False

   With Sheets("Variabelen")
      TempnameA = .Range("D9")                   '= NL14ABCD012345
      TempnameB = .Range("E9")                   '= NL03ABCD016789
   End With

   With Sheets("importRB")
      [COLOR="#FF0000"] Set c1 = .Range("A7:A" & Application.Max(8, .Range("A" & Rows.Count).End(xlUp).Row)).Resize(, 4) 'rij 7=koprij en 4 kolommen
       MsgBox c1.Address'eventjes bereik vermelden

      Select Case c1.Cells(2, 1).Value    [/COLOR]          '
         Case TempnameA
            With c1
               .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(3, 7), "m-d-yyyy")
               '.Offset(1).EntireRow.Delete       'tijdelijk uitgeschakeld voor test
               .AutoFilter
            End With

         Case TempnameB
            With c1
               .AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(4, 7), "m-d-yyyy")
               .Offset(1).EntireRow.Delete
               .AutoFilter
            End With
      End Select
   End With

   Application.ScreenUpdating = True

   ' Call RB105_Formules_kopiëren

End Sub

 

[anton44]

Perfect.
Met gewaardeerde hulp !

 

[anton44]

Voor de begripsvorming:
Als cel A7 leeg blijft is er dan een voorkeur voor #2 of #4 ?

Aanpassing:
Niet alleen A7 moet leeg zijn maar alle cellen in rij 7 moeten leeg. Dit kan in mijn toepassing niet.
Ergo, #4 is de juiste keuze.

 

[cow18]

met currentregion gaat het direct over meer dan "is A7 leeg of niet".
Daarmee maak je een aaneensluitende blok (rechthoek) van gebruikte cellen en dat zou wel eens ongewenste en gevaarlijke situaties kunnen opleveren, omdat je plots met je koprij hoger dan de 7e rij zou kunnen uitkomen.
Vul anders eens A1 tot A7 met iets en vraag dan het bereik van currentregion af !
Dus #2 of #4 als de 7e rij echt leeg is en dat is meer dan enkel A7.

Neem voor de veiligheid #7

 

[anton44]

Alweer bedankt voor uitleg en advies. :thumb: :thumb:
Dat hele rij 7 leeg moet zijn daar was ik inmiddels achter - zie aanpassing in #9
Ook na vullen A1: I7 blijft de currentregion hetzelfde. Keuze gemaakt en zeer tevreden.