Lege cellen uit een bereik verwijderen

Formulering van het probleem

We hebben een reeks cellen met gegevens die lege cellen bevatten:

 

De taak is om lege cellen te verwijderen, zodat alleen cellen met informatie overblijven.

Methode 1. Ruw en snel

  1. Het originele bereik selecteren
  2. Druk op de toets F5, volgende knop Markeer (Speciaal). Selecteer in het geopende venster lege cellen(Spaties) en klik op OK.

    Lege cellen uit een bereik verwijderen

    Alle lege cellen in het bereik zijn geselecteerd.

  3. We geven een commando in het menu om geselecteerde cellen te verwijderen: klik met de rechtermuisknop- Cellen verwijderen (Verwijder cellen) met opwaartse verschuiving.

Methode 2: Matrixformule

Laten we ter vereenvoudiging onze werkbereiken een naam geven met Name Manager (Naam Manager) tab formule (Formules) of, in Excel 2003 en ouder, het menu Invoegen – Naam – Toewijzen (Invoegen — Naam — Definiëren)

 

Noem het bereik B3:B10 Heb Leeg, bereik D3:D10 – GeenLeeg. Bereiken moeten strikt even groot zijn en kunnen overal ten opzichte van elkaar worden geplaatst.

Selecteer nu de eerste cel van het tweede bereik (D3) en voer deze enge formule erin in:

=ALS(RIJ() -RIJ(GeenLeeg)+1>NOTROWS(JaLeeg)-COUNTBLANK(JaLeeg);””;INDIRECT(ADRES(LAAGSTE((IF(Leeg<>“”,RIJ(Leeg);RIJ() + RIJEN(er zijn leeg))); LINE()-ROW(niet leeg)+1); COLUMN(er zijn leeg); 4)))

In de Engelse versie wordt het:

=ALS(RIJ()-RIJ(GeenLeeg)+1>RIJEN(Leeg)-AANTALBLANK(Leeg),””,INDIRECT(ADRES(SMALL((IF(Leeg<>“”,RIJ(Leeg),RIJ() +RIJEN(HebbenLeeg))),RIJ()-RIJ(GeenLeeg)+1),KOLOM(HebbenLeeg),4)))

Bovendien moet het worden ingevoerd als een matrixformule, dwz druk na het plakken Enter (zoals gewoonlijk) en Ctrl + Shift + Enter. Nu kan de formule naar beneden worden gekopieerd met behulp van autocomplete (sleep het zwarte kruis in de rechterbenedenhoek van de cel) - en we krijgen het originele bereik, maar zonder lege cellen:

 

Methode 3. Aangepaste functie in VBA

Als het vermoeden bestaat dat je de procedure voor het verwijderen van lege cellen uit reeksen vaak zult moeten herhalen, dan is het beter om je eigen functie voor het verwijderen van lege cellen eenmalig toe te voegen aan de standaardset en deze in alle volgende gevallen te gebruiken.

Open hiervoor de Visual Basic Editor (ALT + F11), voeg een nieuwe lege module in (menu Invoegen – Module) en kopieer de tekst van deze functie daar:

Functie NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Resultaat(1 tot MaxCells, 1 tot 1) Voor elke Rng in DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Resultaat(N, 1 ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Resultaat End If End-functie

Vergeet niet het bestand op te slaan en terug te schakelen van de Visual Basic Editor naar Excel. Om deze functie in ons voorbeeld te gebruiken:

  1. Selecteer een voldoende bereik van lege cellen, bijvoorbeeld F3:F10.
  2. Ga naar het menu Invoegen – Functie (Invoegen — Functie)of klik op de knop Functie invoegen (functie invoegen) tab formule (Formules) in nieuwere versies van Excel. In categorie User Defined (Gebruiker gedefinieerde) kies onze functie Geen blanco's.
  3. Specificeer het bronbereik met voids (B3:B10) als het functieargument en druk op Ctrl + Shift + Enterom de functie als matrixformule in te voeren.

:

  • Alle lege rijen in een tabel in één keer verwijderen met een eenvoudige macro
  • Alle lege rijen in een werkblad in één keer verwijderen met de PLEX-add-on
  • Snel alle lege cellen vullen
  • Wat zijn macro's, waar macrocode in VBA invoegen?

 

Laat een reactie achter