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

Leere Zellen u. Nullen ausschließen

Forumthread: Leere Zellen u. Nullen ausschließen

Leere Zellen u. Nullen ausschließen
03.11.2016 11:37:45
Max2
Hallo Leute,
kennt ihr eine Möglichkeit in VBA leere Zellen aus Berechnungen auszulassen ?
Ich habe im Internet nichts gescheites dazu finden können außer Excel Formeln.
Es geht um folgenden Code:

lngZeile_2 = .Range(.Cells(.Rows.Count, 1), .Cells(1, 1)).Find(What:="*", LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngAnzSpa = .Range(.Cells(lngZeile_2, .Columns.Count), .Cells(1, 1)).Find(What:="*", LookIn: _
= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lngZeilenBlock = 50
ReDim arrErgebnis(1 To CLng(lngZeile_2 / lngZeilenBlock) + 1, 1 To 6)
lngZeile_3 = 0
 For lngZeile = 1 To lngZeile_2 Step lngZeilenBlock
Set rngBlock = .Range(.Cells(lngZeile), .Cells(lngZeile + lngZeilenBlock - 1, lngAnzSpa) _
)
            lngZeile_3 = lngZeile_3 + 1
With Application.WorksheetFunction
arrErgebnis(lngZeile_3, 1) = lngZeile_3
arrErgebnis(lngZeile_3, 2) = .Min(rngBlock)
arrErgebnis(lngZeile_3, 3) = .Average(rngBlock)
arrErgebnis(lngZeile_3, 4) = .StDev(rngBlock)
arrErgebnis(lngZeile_3, 5) = lngZeile
If lngZeile + lngZeilenBlock - 1 > lngZeile_2 Then
arrErgebnis(lngZeile_3, 6) = lngZeile_2
Else
arrErgebnis(lngZeile_3, 6) = lngZeile + lngZeilenBlock - 1
End If
End With
Der Fettmarkierte Teil ist der Block der berechnet wird, kann ich ihm irgendwie vermitteln dass er den Block nur dann berechnen soll wenn er einen Wert hat UND Nullen beim berechnen zusätzlich ausschließen soll ?
Das Problem ist dass die Tabelle die berechnet wird mehrere Hundert leere Zeilen oder Zellen hat und dann kommen wieder Zellen mit Werten
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Leere Zellen u. Nullen ausschließen
03.11.2016 12:20:46
Michael
Hi,
ein Block hat nicht EINEN Wert, sondern viele: was für "einen" Wert stellst Du Dir vor?
Du verwendest ohnehin "Excel-Formeln" in Deinem Code, z.B. .Min, also warum nicht noch eine, z.B. zählenwenn (nach Leerzeichen oder 0) oder Anzahl?
Leere Zellen kannst Du in VBA mit specialcells ermitteln: zeichne mal mit dem Makrorekorder auf:
- Bereich (mit leeren und gefüllten Zellen) markieren
- F5-Taste (öffnet den Dialog "Gehe zu"),
- dort links unten "Inhalte" anklicken und
- Leerzellen auswählen
(Aufzeichnung beenden nicht vergessen)
Den Code kannst Du dann auf Deinen rngBlock anwenden. Dessen Definition ist wiederum auch etwas unübersichtlich (zumal beim ersten .cells nur ein Wert angegeben wurde); schöner wäre es etwa so: 
Set rngBlock = .Range("A" & lngZeile).resize(lngZeilenBlock,lgnAnzSpa)

.resize vergrößert die eine, angegebene Zelle auf den Bereich mit 50 Zeilen und x Spalten.
Die Ermittlung von lngZeile_2 (lngAnzSpa analog) kommt mir etwas umständlich vor: wenn schon mit .find, dann reicht .rows(1).Find(...), oder Du nimmst
lngZeile_2=.cells(.columns.count,1).end(xltoleft).column
Falls Dir die Infos zu abstrakt sein sollten, lade bitte eine anonymisierte Beispieldatei hoch.
Schöne Grüße,
Michael
Anzeige
AW: Leere Zellen u. Nullen ausschließen
04.11.2016 15:44:49
Max2
Danke für deine Hilfe, habe deine Verbesserungsvorschläge zu Herz genommen, der Code sieht jetzt um einiges aufgeräumter aus, dafür schon mal Danke.
Das mit .Average und .StDev habe ich jetzt durch eine If-Abfrage gelöst:

If .Count(rngBlock) > 0 Then
End If
Klappt alles einwandfrei, Danke dir.
Anzeige
ok, super, danke für die Rückmeldung
04.11.2016 18:07:27
Michael
Gruß,
Michael
Anzeige
Anzeige

Infobox / Tutorial

Leere Zellen und Nullen in Excel VBA ausschließen

Schritt-für-Schritt-Anleitung

  1. VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.

  2. Neues Modul hinzufügen: Klicke mit der rechten Maustaste auf "VBAProject (DeineDateiName)" > Einfügen > Modul.

  3. Code einfügen: Kopiere den folgenden Code in das Modul:

    Sub BerechnungOhneLeereUndNullen()
   Dim lngZeile_2 As Long, lngAnzSpa As Long, lngZeilenBlock As Long
   Dim lngZeile As Long, lngZeile_3 As Long
   Dim arrErgebnis() As Variant
   Dim rngBlock As Range

   ' Finde die letzte Zeile und Spalte
   lngZeile_2 = Cells(Rows.Count, 1).End(xlUp).Row
   lngAnzSpa = Cells(1, Columns.Count).End(xlToLeft).Column
   lngZeilenBlock = 50
   ReDim arrErgebnis(1 To CLng(lngZeile_2 / lngZeilenBlock) + 1, 1 To 6)
   lngZeile_3 = 0

   For lngZeile = 1 To lngZeile_2 Step lngZeilenBlock
       Set rngBlock = Range(Cells(lngZeile, 1), Cells(Application.Min(lngZeile + lngZeilenBlock - 1, lngZeile_2), lngAnzSpa))
       If Application.Count(rngBlock) > 0 Then
           lngZeile_3 = lngZeile_3 + 1
           With Application.WorksheetFunction
               arrErgebnis(lngZeile_3, 1) = lngZeile_3
               arrErgebnis(lngZeile_3, 2) = .Min(rngBlock)
               arrErgebnis(lngZeile_3, 3) = .Average(rngBlock)
               arrErgebnis(lngZeile_3, 4) = .StDev(rngBlock)
               arrErgebnis(lngZeile_3, 5) = lngZeile
               arrErgebnis(lngZeile_3, 6) = Application.Min(lngZeile + lngZeilenBlock - 1, lngZeile_2)
           End With
       End If
   Next lngZeile
End Sub

  4. Makro ausführen: Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle BerechnungOhneLeereUndNullen und klicke auf "Ausführen".

Häufige Fehler und Lösungen

  • Problem: Der Code funktioniert nicht wie erwartet.

    • Lösung: Stelle sicher, dass die zu bearbeitenden Zellen tatsächlich Werte enthalten und keine Formeln, die zu leeren Ergebnissen führen.

  • Problem: Leere Zellen werden nicht ausgeschlossen.

    • Lösung: Nutze Application.Count(rngBlock), um nur Bereiche mit Werten zu berechnen. Laut Michael im Thread ist dies eine effektive Methode.

Alternative Methoden

  • Zählen mit Excel-Formeln: Anstelle von VBA kannst du die Formel =ZÄHLENWENN(Bereich;"<>0") verwenden, um Nullen auszuschließen.
  • Datenfilter: Setze einen Filter auf deine Daten und blende leere Zellen und Nullen aus, bevor du Berechnungen durchführst.

Praktische Beispiele

Wenn du eine Tabelle mit Werten in Spalte A hast und leere Zellen sowie Nullen ausschließen möchtest, kannst du den oben beschriebenen VBA-Code verwenden. Hier ist ein einfaches Beispiel:

A
5
0
10
15
20

In diesem Fall wird das Makro nur die Zellen mit den Werten 5, 10, 15 und 20 berücksichtigen.

Tipps für Profis

  • Verwende SpecialCells(xlCellTypeBlanks), um gezielt leere Zellen innerhalb eines Bereichs zu ermitteln und zu bearbeiten.
  • Nutze das Resize-Attribut, um deinen Bereich dynamisch anzupassen. So kannst du den Code flexibler gestalten.
Set rngBlock = Range("A" & lngZeile).Resize(lngZeilenBlock, lngAnzSpa)

FAQ: Häufige Fragen

1. Frage Wie kann ich sicherstellen, dass nur Zellen mit tatsächlichen Werten betrachtet werden?

Antwort: Verwende die Application.Count-Funktion, um nur Zellen mit Werten zu zählen und diese in deinen Berechnungen zu berücksichtigen.

2. Frage Kann ich den Code auch für andere Spalten verwenden?

Antwort: Ja, passe einfach die Range-Referenzen im Code an die gewünschte Spalte an.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige