Ga naar hoofdinhoud

Hoe vind en krijg ik de waarde van de eerste zichtbare cel na filteren in Excel?

In sommige gevallen moet u mogelijk de waarde van de eerste zichtbare cel in een gefilterde lijst ophalen. Hoe bereik je dit? Dit artikel laat je methoden zien om het op te lossen.

Zoek en verkrijg de waarde van de eerste zichtbare cel na filteren met matrixformule
Zoek en verkrijg de waarde van de eerste zichtbare cel na filteren met VBA


Zoek en verkrijg de waarde van de eerste zichtbare cel na filteren met matrixformule

U kunt de volgende matrixformule toepassen om de waarde van de eerste zichtbare cel in een gefilterde lijst op te halen. Ga als volgt te werk.

1. Selecteer een lege cel om de eerste zichtbare waarde in te plaatsen, voer de onderstaande formule erin in en druk op Ctrl + Shift + Enter toetsen tegelijk.

=INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1)))

Note: In de formule is C2: C19 de gefilterde lijst waarvan u de eerste zichtbare celwaarde wilt retourneren, C2 is de eerste cel van de lijst. U kunt ze naar behoefte wijzigen.

Vervolgens kunt u de waarde van de eerste zichtbare cel in de gefilterde lijst zien die in de geselecteerde cel is ingevuld, zoals hierboven weergegeven.


Zoek en verkrijg de waarde van de eerste zichtbare cel na filteren met VBA

Naast het gebruik van de bovenstaande matrixformule om de waarde van de eerste zichtbare cel in een gefilterde lijst te krijgen, kunt u het onderstaande VBA-script uitvoeren om snel de eerste zichtbare cel van een gefilterde lijst te retourneren. Ga als volgt te werk.

1. Selecteer een lege cel zoals E8 om de eerste zichtbare celwaarde van een gefilterde lijst te plaatsen.

2. druk op anders + F11 toetsen tegelijkertijd om het Microsoft Visual Basic voor toepassingen venster.

3. In de Microsoft Visual Basic voor toepassingen venster klikt Invoegen > Module. Kopieer en plak vervolgens de onderstaande VBA-code in het modulevenster.

VBA-code: zoek en verkrijg de waarde van de eerste zichtbare cel na filteren in Excel

Sub FirstVisibleCell()
   With Worksheets("Sheet1").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
End Sub

Note: In de code is Blad1 de bladnaam die de gefilterde lijst bevat. "C" is de kolomnaam waarvan u de eerste zichtbare waarde wilt ophalen. U kunt ze naar behoefte wijzigen.

3. druk de F5 toets om de code uit te voeren, dan wordt de eerste zichtbare cel van de gefilterde lijst die zich in kolom C bevindt, onmiddellijk in cel E8 ingevuld.

Beste Office-productiviteitstools

馃 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar effici毛ntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

Omschrijving


Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks a lot, that formula INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1))) literally save my soul.
Even though I still do not get how the formula works, it does provide the return that I would like to have.

Regards,
Vedat
This comment was minimized by the moderator on the site
Thank you!
This comment was minimized by the moderator on the site
In the following VBA code, can you please explain what does SpecialCells(xlCellTypeVisible)(1) mean? What is (1) used for after SpecialCells(xlCellTypeVisible)?
ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
This comment was minimized by the moderator on the site
Can you help me write this formula in google sheet with arrayformula ()

{=INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1)))}
This comment was minimized by the moderator on the site
So for me the answer above simply does't work that well. I have a workbook with over 23k rows and is something I've been working to optimize over the years. My solution came to me this morning in yet another spark of inspiration.
Assuming that you are using a TABLE to get the data you want do this:One row (I named it "Rownmbr") is dedicated to: =CELL([@[YOURFIRSTROW]])
Outside the table you make a formula somewhere:=INDIRECT(CONCATENATE("A";SUBTOTAL(105;Table1[Rownmbr])))
You simply replace the "A" with wherever you want to get the data from. For me this works great, and it's not a heavy formula to use that will bog down the document further.
This comment was minimized by the moderator on the site
This solution worked perfectly for me, since I was using a table. The instructions were hard to follow, so hopefully this explanation will be a bit clearer.
Say you have an excel table named "Table_Name."  First, create a new column in your table and name it "RowNumber". 
In that new column, enter the formula "=ROW([@RowNumber])" This will then populate the new column with
In the cell where you want to display the first visible value, enter the formula "=INDIRECT("A"&SUBTOTAL(105,Table_Name[RowNumber]))"  The "A" is the letter of the column that holds the value you want to return.
The SUBTOTAL(105,...) function returns the minimum value of all the visible rows in a given range, which in the case above will return the row number of the first visible row.
The INDIRECT function returns the value of the cell for a given cell address. This is why you need the first part of the formula to contain the column letter of the column you want to display. The second part of the formula will return the row number.


This comment was minimized by the moderator on the site
This formula works, but be aware that if you have blank cells, the formula can malfunction and select the first value in the entire table, not just in the filtered results. The workaround I found was to replace blank cells with NULL or a space.
This comment was minimized by the moderator on the site
First I had blanks.... then all cells were filled but still having the #VALUE response. Hopefully this formula works on text as well...! Thanks for the advise anyway ;-)
This comment was minimized by the moderator on the site
It works. Thanks so much...
This comment was minimized by the moderator on the site
Thanks a million馃檹馃尯
This comment was minimized by the moderator on the site
鈥寃onderful馃尯 Thanks a million馃檹
This comment was minimized by the moderator on the site
I keep getting a Run-time error '91' Object variable or With block variable not set. My worksheet is Sheet1 titled AllBrands. This is my formula :

Sub FirstVisibleCell()
With Worksheets("ALLBRANDS").AutoFilter.Range
ActiveCell.Value2 = Range("H" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

End Sub

Any help will be appreciated!
This comment was minimized by the moderator on the site
Unfortunately - neither the formula - that gives me a #VALUE error nor the VBA works for me giving me a run-time error '1004'
Too bad as I have Office 2019 and I was expecting one of these features to work when filtering specific cells... :-(
This comment was minimized by the moderator on the site
Hi Bub,
The code works well in my case. Which Excel version are you using?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations