Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
2020to2024
2020to2024
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Anzeige
Inhaltsverzeichnis

Benannte Bereiche durchsuchen

Forumthread: Benannte Bereiche durchsuchen

Benannte Bereiche durchsuchen
23.10.2025 12:01:30
13uro
Hallo

Ich habe in einem Tabellenblatt mehrere Teile mit Überschrift und
möchte diese in einer Spalte auf nichtleere Zellen durchsuchen.
Sind nichtleere Zellen vorhanden wird in der Überschriftszeile ein Eintrag gemacht.
Bisher mache ich das mit diesem Coding: 
With ThisWorkbook.Worksheets(„Tabelle1“)

 If WorksheetFunction.CountA(.Range(Cells(4, 6), Cells(8,6))) > 0 Then

.Cells(3, 6).Value = "X"

Else

.Cells(3,6).Value = ""

End If

End with

Nachteil:
Das muss dann aber mehrfach für alle Teile gemacht werden und
es können innerhalb der Teile Zeilen gelöscht oder hinzugefügt werden,
was dann die Zellbezeichnung ändert.

Ich habe jetzt versucht das mit benannten Bereichen zu lösen, habe aber keinen Plan wie das zu machen ist?
Müssen die Bereiche dann in ein Array eingelesen werden und dann die Arrayelemente in die Schleife?

Ich habe zur Verdeutlichung eine kleine Beispielmappe angehängt.
Vlt. kann mich hier jemand auf den Weg bringen.
Gruß Uli


https://www.herber.de/bbs/user/179337.xlsm
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 12:44:53
daniel
Hi

prinzipiell kannst du benannte Bereiche in VBA über Range("Name des Bereichs") referenzieren.
hier ein Code der in einer Schleife über alle benannten Bereiche läuft und die von dir beschriebene Prüfung durchführt: 

Sub test()

Dim nme As Name

For Each nme In ThisWorkbook.Names

    With Range(nme.Name)

        With .Columns(6)

            Intersect(.Offset(0, 0), .Offset(1, 0)).Select

            If WorksheetFunction.CountA(Intersect(.Offset(0, 0), .Offset(1, 0))) Then

                .Cells(1, 1).Value = "x"

            Else

                .Cells(1, 1).Value = ""

            End If

        End With

    End With

Next

End Sub


die Frage ist allerdings, warum du nicht einfach in die jeweilige Überschriftenzeile eine entsprechende Formel einfügst.
die müsste sich genauso die der Name automatisch anpassen, wenn Zeilen gelöscht oder eingefügt werden, wobei du beachten musst, dass das einfügen immer zwischen erster und letzter Zeile des Bereichs erfolgen muss und nicht ober- oder unterhalb von diesen.

Gruß Daniel

Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 15:31:16
schauan
mal noch ein Hinweis. Du solltest den Hydraulik-Bereich bis Zeile 30 gehen lassen und nicht nur 29. Bei den anderen hast Du ja auch die Leerzeile unter den Daten genommen. Siehe dazu auch der Hinweis von Daniel zum Einfügen und Löschen. Das ist in dem Zusammenhang eben der Vorteil, wenn der Bereich über den Daten beginnt und darunter endet.

Ich habe die Datei mal runter geladen. Mein Vorschlag kommt ohne benannte Bereiche zurecht. Voraussetzung ist, dass in Spalte A zwischen zwei Datenblöcken immer eine Zelle leer ist. 

Sub test()

'Beginnt in Zeile 3

zeile = 3

With ActiveSheet

'Schleife ueber alle Zeilen des benutzten Bereichs

For Each zeilen In .UsedRange.Rows

  'wenn es die "Ueberschriften-Zeile" ist, dann

  If zeilen.Row = zeile Then

    'Datenbereich bis zur naechsten freien Zelle in Spalte A festlegen

    Set Rng = .Range(.Cells(zeile + 1, 1), .Cells(zeile + 1, 1).End(xlDown))

    'wenn was da steht, dann in Ueberschriftenzelle ein X setzen

    If WorksheetFunction.CountA(Rng.Offset(, 5)) > 0 Then 'offset von Spalte A zu F = 5

      .Cells(zeile, 6) = "X" 'auch hier statt fixer 6 natuerlich auch offset(, 5) moeglich. 

                             'Wird statt 5 eine Variable verwendet, koennen weitere Spalten in einer Schleife behandelt werden.  

    'oder wenn nicht, Uberschriftenzelle leeren

    Else

      .Cells(zeile, 6) = ""

    End If

    'Zeilenzahl um 2 erhoehen - von zuletzt verwendeter Datenzeile zur naechsten Ueberschriftenzeile

    zeile = .Cells(zeile + 1, 1).End(xlDown).Row + 2

  End If

'If zeile >= 100 Then Stop - nur zur Sicherheit :-)

Next

End With

End Sub

Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 15:58:11
13uro
Hallo zusammen

danke für eurer Hilfe!
Wie angegeben bin ich noch kein VBA-Crack,
also ich muß das alles mal für mich durchgehen ob ich damit klar komme.
Wo ich noch nicht ganz durchblicke sind diese Zeilen 
Intersect(.Offset(0, 0), .Offset(1, 0)).Select

            If WorksheetFunction.CountA(Intersect(.Offset(0, 0), .Offset(1, 0))) Then

                .Cells(1, 1).Value = "x"

Werden hier nur zwei Zellen (0,0) und (1,0) durchsucht ???

@Schauan
Die jeweiligen Bereich enthalten immer am Schluß eine Leerzeile wg. Zeilen einfügen.
Das habe ich wohl in der Beispielmappe übersehen, trotzden Danke.
Und richtig, es werden noch mehrere Spalten durchsucht, aber diese Erweiterung
krieg ich denke ich hin, die ist in meiner Fassung mit den ganzen Einzelschleifen schon drin.

Wenn noch weitere Fragen auftachen melde ich mich nochmal
Uli
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 16:14:05
daniel
Hi

naja, du hast die Überschriftenzeile, in welcher das "x" eingetragen werden soll, im benannten Bereich mit drin.
diese darf aber nicht mit ausgewertet werden, sonst verschwindet das "x" nicht, wenn es aus einer alten Auswertung noch drin steht, inzwischen aber in den darunterliegenden Zellen alle "x" entfernt wurden, weil dann ja im ausgewerteten Bereich ein "x" vorhanden ist.

OFFSET verscheibt den Bereich, dh Range("A1:A10").Offset(1, 0) ergibt Range("A2:A11")
INTERSECT bildet die Schnittmenge aus zwei Bereichen.
Intersect(Range("A1:A10"), Range("A2:A11")) ergibt Range("A2:A10")
dh mit diesem Codeteil entferne ich die Überschriftenzeile aus dem Bereich.
ein Offset allein würde zwar die erste Zeile entfernen, aber die darunter liegende Zeile mit hinzunehmen, was ja auch wieder zu Fehlern führen könnte.

die Zeile mit dem .Select kannst du löschen rausnehmen
die ist nur dafür da, um zu prüfen ob man auch den richtigen Zellbereich verwendet, wenn man den Code im Einzelstepmodus testet.

Gruß Daniel
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 13:08:14
schauan
... oder so was: 

With ActiveSheet

For Each spalten In .UsedRange.Columns

  Set Rng = .Range(.Cells(4, .Range(spalten.Address).Column), .Cells(8, .Range(spalten.Address).Column))

If WorksheetFunction.CountA(Rng) > 0 Then

  MsgBox Rng.Address

Else

  MsgBox Rng.Address & " leer"

End If

Next

End With
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 13:25:44
13uro
Hallo Daniel

Danke für die Antwort.
Das mit der Formel habe ich auch schon gedacht, das hat aber in der
weiteren Verarbeitung (die problemlos funktioniert) bei der ich alle Zeilen
die in der jeweiligen Suchspalte nichtleere Zellen enthalten, in ein neues
Tabellenblatt kopiere den Nachteil das diese Zelle mit Formel ja dann immer
als nichtleer interpretiert wird und mitkopiert wird auch wenn alle Zellen leer sind.

Ich werde mal schauen ob ich mit deinem Coding klar komme

Uli
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 14:16:01
JoWE
Hallo Daniel,
eigentlich soll doch lediglich die 6. Spalte des benannten Bereichs (hier Spalte F)
auf leere Zellen geprüft werden. Wie würde denn da die dynamische Formel aussehen?
So ists ja halt nicht dynamisch: =WENN(ANZAHLLEEREZELLEN((F3:F9))>0;"x";"")
Gruß
Jochen
AW: Benannte Bereiche durchsuchen
23.10.2025 14:47:39
daniel
so ist es nicht dynamisch, passt sich aber in der Regel auf Veränderungen wie Einfügen und Löschen von Zellen an.
dynamisch bezogen auf den Namen analog des Makros wäre INDEX(name;0;6) (6. Spalte des benannten Bereichs).
allerdings ist da dann die erste Zeile noch mit drin, die ja in bezug auf das "x" nicht ausgewertet werden darf.

Gruß Daniel
Anzeige
AW: Benannte Bereiche durchsuchen
23.10.2025 14:50:48
JoWE
thx Daniel, darauf war ich gerade nicht gekommen.
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.05.2026 13:31:09
Zeichen ausblenden
14.05.2026 09:50:42
Makro für MacOs
13.05.2026 19:14:18
Gleichheitszeichen
13.05.2026 14:24:56
VBA Projekt Passwort automatisch eingeben
13.05.2026 08:49:41
Reale Berechnung ohne Nachkommawerte
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum