Microsoft Excel

Herbers Excel/VBA-Archiv

Ganze Zeile löschen wenn Wert nicht vorhanden

Betrifft: Ganze Zeile löschen wenn Wert nicht vorhanden von: Chris
Geschrieben am: 31.07.2014 11:12:02

Hallo, zusammen!

Ich habe folgendes Problem:

In meiner Tabelle1 stehen im Bereich C5:Cx Zahlen. Ich möchte per VBA die ganze Zeile löschen, wenn die Zahlen in C nicht in Tabelle2 Bereich A2:Ay (selbe Datei) vorkommen. Ich habe es mit Find versucht, komme aber nicht wirklich weiter.

Vielen Dank schon mal vorab für Eure Hilfe

  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: ChrisL
Geschrieben am: 31.07.2014 12:55:45

Hi Chris

Sub t()
Dim i As Long
Dim WS1 As Worksheet, WS2 As Worksheet

Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")

For i = WS1.Range("C65536").End(xlUp).Row To 5 Step -1
    If WorksheetFunction.CountIf(WS2.Columns(3), WS1.Cells(i, 3)) = 0 Then _
    WS1.Rows(i).EntireRow.Delete
Next i
End Sub
cu
Chris


  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: Werner
Geschrieben am: 31.07.2014 13:32:05

Hallo Chris,

obwohl das "nicht" fett geschrieben ist habe ich es übersehen. Bei mir löscht er in Tabelle 1 wenn die Zahl in Tabelle 2 vorhanden ist. Da müsste lZelle=sZelle in lZelle<>sZelle geändert und die Msg-Boxen angepasst werden.

Werner


  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: Robert
Geschrieben am: 31.07.2014 12:57:07

Hallo Chris,

Ich gehe davon aus, dass du mit dem Bereich C5:Cx die Gesamte Spalte C meinst, beginnend bei Zeile 5.
Bei A2:Ay dementsprechend von Zeile 2 bis Ende.

Option Explicit

Dim i, o As Integer
Dim Matchfound As Boolean

Private Sub Test()
    For i = Tabelle1.UsedRange.SpecialCells(xlCellTypeLastCell).Row To 5 Step -1
        Matchfound = False
        For o = Tabelle2.UsedRange.SpecialCells(xlCellTypeLastCell).Row To 2 Step -1
            If Tabelle1.Cells(i, 3) = Tabelle2.Cells(o, 1) Then
                Matchfound = True
                Exit For
            End If
        Next o
        If Matchfound = False Then Tabelle1.Rows(i).Delete
    Next i
End Sub



  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: Chris
Geschrieben am: 31.07.2014 14:12:52

Hallo, Robert!

Danke, funktioniert super!


  

Betrifft: und noch ein Ansatz von: Michael
Geschrieben am: 31.07.2014 13:17:22

Hallo Leute,

ich hab es mal mit einer Matrixformel versucht:

D	Erg	F
1	1	1
2	1	2
3	1	3
6	0	4
7	0	5

die da lautet: {=SUMME(($F$16:$F$20=D16)*1)}
und in jeder Zeile unter E steht - muß natürlich an die entsprechenden Bereiche angepaßt werden.
Hinterher ist nur noch ein Schleifendurchlauf nach Erg=0 -> Löschen nötig.

Ich bin, ehrlich gesagt, ganz stolz, daß ich das Ding hinbekommen hab; ob es in der Praxis taugt, kann vielleicht jemand anderes beantworten: x-100 mal suchen dauert, aber ich vermute, daß die Matrixformel bei größeren Datenmengen eben auch Zeit benötigt.

Happy Exceling,

Michael


  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: Werner
Geschrieben am: 31.07.2014 13:24:53

Hallo Chris,

so??

https://www.herber.de/bbs/user/91812.xlsm


Gruß Werner


  

Betrifft: AW: Ganze Zeile löschen wenn Wert nicht vorhanden von: Daniel
Geschrieben am: 31.07.2014 14:08:36

Hi

Zeilen löschen in Excel ab Version 2007 funktioniert so am einfachsten:
1. in einer Hilfsspalte eine Formel einfügen, die alle Zeilen die gelöscht werden müssen mit 0 kennzeichnet und alle die stehen bleiben sollen mit der Zeilennummer

2. in die Überschriftenzeile kommt ebenfalls die 0

3. Daten - Datentools - Duplikate entfernen auf die Tabelle anwendnen mit der Hilfsspalte als Kriterium und "keine Überschrift"

bei grössern Datenmengen sollte man die Tabelle2 Spalte A aufsteigens sortieren, damit man die schnelle Variante des Sverweises verwenden kann.

Gruß Daniel

sieht für deinen Code so aus:

dim Zelle as Range
set Zelle = Sheets("Tabelle1").Cells.SpecialCells(xlcelltypelastcell).offset(0, 1)
With Range(Zelle.Offset(4 - Zelle.row), Zelle)
    .FormulaR1C1 = "=IF(IsError(Match(RC3,Tabelle2!C1,0)),0,Row())"
    .Formula = .Value
    .Cells(1, 1).Value = 0
    .EntireRow.RemoveDuplicates .Column, xlno
    .Clearcontents
End with



 

Beiträge aus den Excel-Beispielen zum Thema "Ganze Zeile löschen wenn Wert nicht vorhanden"