Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1224to1228
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1224to1228
1224to1228
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

ganze Zeilen löschen mit Specialcells?

ganze Zeilen löschen mit Specialcells?
Peter
Guten Tag
Ich habe in einer Tabelle ein Spaltenbereich, der mit PSSUMME benannt ist. Dieser Bereich umfasst ca 3500 Zeilen und in jeder Zelle steht eine Formel. Nun möchte ich alle Zeilen löschen, bei denen die Formel NULL ergibt. Ich habe versucht, das mit specialcells anzustellen, jedoch ohne Erfolg. Wenn ein bestimmter Wert wie NULL nicht identifiziert werden kann, dann vielleicht ein WAHRHEITSWERT (0)? Aber wie?
Alternativ muss ich die Zeilen einzeln abarbeiten, was sehr zeitaufwändig ist.
Danke für eine Antwort.
Gruss, Peter
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 09:23:34
Tino
Hallo,
kannst mal so versuchen.
Sub Loeschen_0()
Dim oSH As Worksheet, iCalc As Integer
Dim varSuchwert, varCol

'Suchwert 
varSuchwert = 0

'Tabelle anpassen 
Set oSH = Sheets("Tabelle1")

With Application
 iCalc = .Calculation
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
 
     With oSH.UsedRange
        'Überschrift suchen in Zeile 1 
        varCol = Application.Match("PSSUMME", .Rows(1), 0)
        If IsNumeric(varCol) Then
            'Spaltennummer der Überschrift 
            varCol = .Columns(varCol).Column
            With .Columns(.Columns.Count).Offset(0, 1)
                'Suchwert = String Anführungszeichen erweitern 
                If LCase(TypeName(varSuchwert)) = "string" Then varSuchwert = """" & varSuchwert & """"
                'entsprechende Formel 
                .Formula = "=IF(RC" & varCol & "=" & varSuchwert & ",True,ROW())"
                
                oSH.UsedRange.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
                
                On Error Resume Next
                    .SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
                    .EntireColumn.Delete
                On Error GoTo 0
              
            End With
        Else
            MsgBox "Überschrift 'PSSUMME' wurde nicht gefunden!", vbCritical
        End If
     End With
 
 .ScreenUpdating = True
 .Calculation = iCalc
End With
End Sub
Gruß Tino
Anzeige
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 11:31:17
Peter
Hallo Tino
Vielen Dank für den umfangeichen Code. Zuerst habe ich nicht ganz begriffen, was
varCol = Application.Match("PSSUMME", .Rows(1), 0)
soll.
Doch nun vermute ich, dass ich mich nicht genügend klar ausgedrückt habe, mit der Bemerkung, dass der Spaltenbereich mit PSSUMME benannt ist. Hier handelt es sich nicht um eine Überschrift, sondern um einen definierten Namen. PSSUMME bezieht sich auf =Auswertung!$D$10:$D$3486. Wahrscheinlich muss ich dann nicht UsedRange ansprechen, sondern Range("PSSUMME")?
Gruss, Peter
Ja! Gruß owT
03.08.2011 11:39:59
Luc:-?
:-?
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 13:14:39
Tino
Hallo,
versuchen wir es eben so.
Sub Loeschen_0()
Dim oSH As Worksheet, iCalc As Integer
Dim varSuchwert, varCol

'Suchwert 
varSuchwert = 0

'Tabelle anpassen 
Set oSH = Sheets("Tabelle1")

With Application
 iCalc = .Calculation
 .Calculation = xlCalculationManual
 .ScreenUpdating = False

    varCol = oSH.Range("PSSUMME").Column
    With oSH.Range("PSSUMME").EntireRow.Columns(.Columns.Count)
        'Suchwert = String Anführungszeichen erweitern 
        If LCase(TypeName(varSuchwert)) = "string" Then varSuchwert = """" & varSuchwert & """"
        'entsprechende Formel 
        .Formula = "=IF(RC" & varCol & "=" & varSuchwert & ",True,ROW())"
        
        .EntireRow.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
        On Error Resume Next
            .SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
            .EntireColumn.Delete
        On Error GoTo 0
    End With
 
 .ScreenUpdating = True
 .Calculation = iCalc
End With
End Sub
Gruß Tino
Anzeige
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 17:49:53
Peter
Hallo Tino
Vielen Dank für deinen Vorschlag. Ich habe mich jetzt für Rudi's Lösung entschieden.
Gruss, Peter
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 18:09:43
Tino
Hallo,
kein Problem wird halt nur länger brauchen.
Gruß Tino
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 18:14:14
Peter
Hallo Tino
Interessanter Hinweis von dir bezüglich Zeit. Da sollte ich deinen Code wohl etwas besser studieren. Ich habe nicht ganz verstanden, was die Logik hinter dem Code ist. Könntest du mir das kurz erklären?
Danke und Gruss, Peter
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 19:05:05
Tino
Hallo,
ist ganz einfach, es wird mittels Formel (in der letzten Spalte der Tabelle)
ermittelt welche Zelle in PSSUMME den Wert 0 hat,
hat diese den Wert 0 wird WAHR ausgegeben sonst die Zeilennummer.
=WENN($D10=0;WAHR;ZEILE())
Nun wird der Bereich nach der letzten Spalte sortiert und alle Zeilen mit dem Wert 0 hängen zusammen ohne die restliche Folge durcheinander zu bringen,
diese Zeilen mit dem Wahrheitswert werden gelöscht.
Gruß Tino
Anzeige
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 22:44:22
Peter
Hallo Tino
Bei diesem Codeteil stehe ich mir wohl selbst auf der Leitung; weiss nicht genau, was hier mit
'Suchwert = String Anführungszeichen erweitern gemeint ist.
Gruss, Peter
If LCase(TypeName(varSuchwert)) = "string" Then varSuchwert = """" & varSuchwert & """"
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 23:12:31
Tino
Hallo,
dies ist nur falls der Suchbegriff aus welchem Grund auch immer geändert wird.
Wenn ich nach einem String suche, muss ich die Anführungszeichen verdoppeln
damit später in der Formel der Begriff in Anführungszeichen steht.
Beispiel suche nach Text
varSuchwert = "Hallo"
Nun muss ja in der Formel der Text so eingebracht das dieser Begriff in Anführungszeichen steht.
=WENN($D10="Hallo";WAHR;ZEILE())
Ohne diese Codezeile würde in der Formal dies ankommen, dass funktioniert so nicht!
=WENN($D10=Hallo;WAHR;ZEILE())
Gruß Tino
Anzeige
AW: ganze Zeilen löschen mit Specialcells?
04.08.2011 08:21:07
Peter
Hallo Tino
Vielen Dank! Jetzt konnte ich alles nachvollziehen.
Gruss, Peter
mal was anderes
04.08.2011 09:26:56
Tino
Hallo,
ist Dir schon aufgefallen durch das löschen von Zeilen wird auch der Namensbereich geändert?!
Evtl. könntest Du den Bereich über Indirekt definieren,
dann bleibt dieser bestehen auch wenn dazwischen Zeilen gelöscht werden.
Gruß Tino
AW: mal was anderes
04.08.2011 09:31:29
Peter
Hallo Tino
Danke für den Hinweis. Ich habe dies auch schon festgestellt. Da jedoch der Name trotz Löschen intakt bleibt und die verbleibenden Zellinhalte "abdeckt", ist dies hier sogar erwünscht.
Gruss, Peter
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 11:45:41
Rudi
Hallo,
warum filterst du nicht einfach nach 0 und löschst die Zeilen?
Gruß
Rudi
Anzeige
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 11:48:40
Peter
Hallo Rudi
Weil es sich hier nur um einen Baustein in einem VBA-Projekt handelt.
Gruß, Peter
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 12:02:26
Rudi
Hallo, 
Sub Null_loeschen()
Dim rngC As Range, rngDel As Range
For Each rngC In Sheets("Auswertung").Range("PSSUMME")
If rngC = 0 Then
If rngDel Is Nothing Then
Set rngDel = rngC
Else
Set rngDel = Union(rngDel, rngC)
End If
End If
Next
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub

Gruß
Rudi
AW: ganze Zeilen löschen mit Specialcells?
03.08.2011 17:46:59
Peter
Hallo Rudi
Das ist eine clevere Lösung! Vielen Dank und Gruss, Peter
Kurze Version mit Specialcells
03.08.2011 20:08:14
mpb
Hallo Peter,
in den Zellen in PSSUMME stehen Formeln. Sofern die bisher keine Wahrheitswerte zurückgeben, ändere die Formeln wir folgt:
bisher: FORMEL
neu: =WENN((FORMEL)=0;WAHR;FORMEL)
Dann folgendes Makro:
Sub Nullzeilen_weg()
Range("PSSUMME").SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
End Sub
Gruß
Martin
Anzeige
AW: Kurzversion
03.08.2011 22:43:38
Peter
Hallo Martin
Das ist clever! Vielen Dank und Gruss, Peter

312 Forumthreads zu ähnlichen Themen

Zelle nichtleer, dann Spalten ausblenden
kellerkind42

hallo.
Ich tue mich sehr schwer mit folgendem Wunsch:
Im Spaltenbereich A-Z:
Im Zeilenbereich 10 -20:
z.B:
Steht in Spalte C etwas in Zeile 10 oder 11 oder 12.... dann Blende diese Spalte aus!
Kann mir bitte jemand auf die Sprünge helfen?
Gruß Reinhardt.
20.08.2008 07:29:00
Thread-ID: 1003062
6 Beiträge
Anzeige
wenn > Spalte I, dann neue Zeile
Dennis

Hallo Leute,
ich benötige einmal Hilfe eines Experten, da ich noch in den Anfängen stecke.
Ich habe folgendes Problem:
Ich habe eine Liste mit Datensätzen (jeder Datensatz steht für eine Person). Die Datensätze sind unterschiedlich lang. Grds. sind die Datensätze von Spalte A - Spal...
04.09.2008 16:41:00
Thread-ID: 1007458
8 Beiträge
Wenn in Zelle folgender String, dann
mehmet

Hallo Forum,
In Spalte G4:G92 sind viele kodierte Zeichenfolgen.
Fest steht aber, dass diese zu erkennen sind, z.B.
in Zelle G4: METAR EKAH 052150Z AUTO 12005KT 9999NDV DZ OVC080/// 15/14 Q1005=
in Zelle G5: METAR ESSA 051940Z 20006KT CAVOK 11/10 Q1006 NOSIG=
und so weiter.....
06.09.2008 01:08:00
Thread-ID: 1007801
16 Beiträge
Anzeige
Wenn kein Optionsfeld ausgewählt dann MsgBox
Bernhard

Hallo liebe Excel Freunde!
Wer kann mir bitte helfen? Ich habe eine UF mit 2 Optionsfeldern. Ich möchte einen Hinweis mit einer MsgBox bekommen, wenn keines der beiden Optionsfelder ausgewählt wurde.
Wie mache ich das in VBA?
Danke für Eure Hilfe
Gruss
Bernhard
09.09.2008 11:32:23
Thread-ID: 1008391
5 Beiträge
VBA- Autofilter - leere Zeilen löschen
Asits

Hallo,
ich habe in der Spalte "A" Kundennummern stehen.
Ich will per Makro / Autofilter die kompletten Zeilen
derjehnigen Kunden löschen, die noch keine Kundennummer haben.
wie mache ich das ?
Vielen Dank vorab!
17.09.2008 08:48:00
Thread-ID: 1010225
5 Beiträge
Wenn Zelle leer, dann Grafik löschen
TomTom

Hi,
ich möchte auf meinem Sheet abprüfen, ob die Zelle A41 und B41 leer sind, und wenn ja, soll eine Grafik (.bmp / wurde eingefügt mit Kopieren und einfügen und heisst im Namensfeld "Bild 01")
gelöscht oder unsichtbar werden.
Wie mache ich dies ?
Danke mal
Gruss
Tom<...
22.09.2008 08:28:10
Thread-ID: 1011373
3 Beiträge
Makro unterbrechen wenn bestimmte Zelle leer
WalterK

Guten Morgen,
wer kann mir beim folgenden Code eine kleine Änderung durchführen:
1.) das Makro sollte abgebrochen werden, wenn im Blatt "Daten" die Zelle "AB12" leer ist und
2.) es sollte eine MsgBox mit dem Hinweis kommen, dass die Eingabe bei "AB12" fehlt und das sollte mit OK bes...
03.10.2008 07:29:45
Thread-ID: 1013985
10 Beiträge
Zählen wenn Zelle nicht leer
Tim

Hallo Forum,
wie kann ich in einem Bereich (z.B. A1:A50) die Anzahl der Zellen, in denen ein Eintrag vorhanden ist (Text oder Zahl) zählen?
Gruß
Tim
20.10.2008 11:47:00
Thread-ID: 1017617
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
5
7
15
5
4
4
2
9
2
2
7
3
7
3
7
1
3
4
23
4
7
4
3
7