Schleife durch den Bereich von Zellen, und die Farbe ändern, wenn ein Wert in diesem Bereich mehr als 3 mal

Ich habe eine Spalte mit verschiedenen zahlen. Mein code ist, diese zu Sortieren und prüfen, wie oft die gleiche Zahl erscheint in dieser Spalte. Wenn ein Wert angezeigt wird, mehr als 3 mal sollte es die Farbe alle Zeilen mit diesem Wert liegen, sonst werden die Zeilen gelöscht werden sollen.

Hier ist mein code bisher:

Sub mySub10()

Dim wsTEMP As Worksheet
Dim wsSPECIAL As Worksheet
Dim wsTEMPLrow As Long
Dim i As Integer
Dim x As Integer
Dim rng As Range

Set wsTEMP = ThisWorkbook.Sheets("Temp")
Set wsSPECIAL = ThisWorkbook.Sheets("Spezial")

Application.ScreenUpdating = False

wsTEMPLrow = Worksheets("Temp").Range("A" & Worksheets("Temp").Rows.Count).End(xlUp).Row

With wsTEMP

  .Columns("A:Q").Sort Key1:=.Range("L1"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

For i = wsTEMPLrow To 5 Step -1
    Set rng = Range("A" & i)
    If Cells(i, 12).Value = Cells(i - 1, 12).Value And Cells(i, 12).Value = Cells(i - 2, 12).Value And Cells(i, 12).Value = Cells(i - 3, 12).Value And Cells(i, 12).Value = Cells(i - 4, 12).Value Then
            Range("A" & i).EntireRow.Interior.ColorIndex = 6
            Range("A" & i - 1).EntireRow.Interior.ColorIndex = 6
    End If
Next

For i = wsTEMPLrow To 2 Step -1
    Set rng = Range("A" & i)
    If rng.Interior.ColorIndex <> 6 Then
        rng.EntireRow.Delete
    End If
Next        

End With

End Sub

Und was ist das problem mit deinem code?

InformationsquelleAutor DEFCON123 | 2014-01-08

1

Hier ist ein Weg, es zu tun. Fügen Sie zunächst eine COUNTIF Formel in einer anderen Spalte. Dies wird zeigen, wie oft jede Zahl erscheint in Spalte A. Hier ein Beispiel dieses mit einem kleinen Datensatz. Die Formel in Zelle B2 ist =COUNTIF($A$2:$A$15,A2) und es ist nach unten kopiert werden manuell-oder Sie können dies tun, in VBA:
```
Range("B2:B15").Formula = "=COUNTIF($A$2:$A$15, $A2)"
```
Habe ich eine bedingte Formatierung für Spalte A markieren Sie die Werte, deren Anzahl von 3 oder mehr.

Dann können Sie löschen Sie die Zeilen, wo die Anzahl kleiner als 3:
```
Dim r As Range
Dim i As Long
Set r = Range("B2:B15")
For i = r.Rows.Count To 1 Step -1
    With r.Cells(i, 1)
        If .Value < 3 Then
            .EntireRow.Delete
        End If
    End With
Next i
```
Ergebnis:
- Danke @Jean. Könnten Sie bitte lassen Sie mich wissen, wie ich das implementieren kann, die ZÄHLENWENN-Funktion für jede Zelle in Spalte R? Das Blatt wird temporäres Blatt vor und ich will nicht, um die Formel manuell ein.
- Ich denke mal du meinst, wie man das mit VBA? Ich habe jetzt Hinzugefügt, dass oben in meiner Antwort. Wenn Sie möchten diese in Spalte R, ersetzen Sie einfach alle B mit R ist.
- Corbett vielen Dank für deine Hilfe. Noch eine Frage: Mein code Range("R2:R" & Lrow).Formel = "=ZÄHLENWENN($L$2:$L$15, $L2)" Wie kann ich $L$15 dynamisch entsprechend der Lrow, wie gut?
- Pflege zu erraten? 🙂 Es ist ziemlich ähnlich zu dem, was hast du für Spalte R auf der linken Seite der Zuweisung... Wenn du die Antwort finden Sie oben nützlich, bitte beachten Sie upvoting/akzeptieren.
- Corbett ich verwendet, Range("R2:R" & Lrow).Formel = "=ZÄHLENWENN($L:$L, $L2)" und es funktioniert jetzt. Ich habe bereits von Ihnen positiv bewertet werden und, dass ich jetzt die Antwort gefunden, ich kann es akzeptieren als die Antwort 🙂
- Super!!! Ich denke, das würde auch funktionieren: .Formula = "=COUNTIF($L$2:$L$" & Lrow & ", $L2)"
- Corbett gibt es eine Möglichkeit, zu beschleunigen, um die Leistung für die Zeilen löschen oder könnte jemand denken, der irgendeine Art von Lösung?? Ich habe über 10k Zeilen von Daten, und es ist furchtbar langsam. Es dauert ewig - etwa 20 Minuten für die ersten 2k Zeilen
- Ich fand einen workaround für mich. Ich bestellte die Daten durch die absteigenden zahlen auf und lief eine for-Schleife. Wenn die Bedingung .Wert > 3 nicht erfüllt, lasse ich den code wählen Sie alle Daten unten an und löschen Sie es. So ist schneller dann die Schleife durch jede Zeile und löschen jeder einzelnen Zeile.
- Recht, weil Ihre Daten sortiert... ich vermisste, dass etwas. Schön. Ansonsten kann man sicherlich beschleunigen mit Application.ScreenUpdating = False an der Spitze Ihres Codes. Gibt es andere tricks, auch. Durchsuchen Sie diese Website und finden Sie Antworten.
- Deaktivieren der Screenupdating war das erste was ich Tat, ohne jeden vernünftigen Effekt. 🙂
- Mit 10k Zeilen in Excel in der Regel bedeutet, dass Sie es verwenden für etwas, das es nicht entwickelt wurde, um. Vielleicht mit MS Access oder eine ausgewachsene Datenbank (SQLite als leichte one) wäre die beste Lösung für Sie. In SQL (Datenbank-Abfrage-Sprache, umgesetzt werden, die von MS Access auch), das Sortieren, das zählen von eindeutigen Werten und viele andere Operationen werden einfach gemacht in nur einer Abfrage.
- Und das ist, warum Excel hat 1M Zeilen zur Verfügung, ab 65k im Jahr 2003?
- Die Fähigkeit, ein Programm ist nicht etwas zu sagen über die unterstützten use cases. Weiß nicht, die design-Entscheidung hinter dem Großen Tisch. Aber was lese ich auf der MSDN-viele performance Verbesserungen gemacht wurden auf Excel zu bewältigen wie ein großes Netz. Aber mein primäres Ziel war es nicht zu sagen, dass Excel ist von Natur aus schlecht oder langsam. Ich wollte zeigen einige alternativen, die vielleicht besser geeignet für komplexere Szenarien und größere Daten.
InformationsquelleAutor Jean-François Corbett
6

Die Hervorhebung Teil erreicht werden kann, mit bedingte Formatierung und ZÄHLENWENN. Nur die Löschung hat zu erfolgen durch VBA.

Ich nehme an, die Spalte mit den zahlen (Spalte A.

COUNTIF zählt die Anzahl der vorkommen

Zählen der Anzahl der vorkommen eines Wertes in Spalte A, verwenden Sie einfach diese Formel in der ersten Zeile einer Spalte und füllen Sie die gesamte Spalte mit:
```
=COUNTIF(A:A, A1)
```
COUNTIF zählt alle Werte im angegebenen Bereich (erstes argument), die die Bedingung erfüllen gegeben wird (zweites argument). A:A ist die ganze Spalte A. Die Angabe Zellbezug als Bedingung zu COUNTIF bedeutet, dass die vorkommen den gleichen Wert gezählt werden soll.

Können Sie jetzt löschen Sie die Spalte mit COUNTIF Formel als es war verwendet nur für Demo, wie COUNTIF funktioniert. Es werden nicht mehr benötigt.

Bedingte Formatierung

Mithilfe der Formel oben geschrieben, ist es möglich, bedingt formatieren von Zellen mit Werten wiederholen mehr als drei mal. Indem Sie die Spalte Referenzen absolut, es ist möglich, formatieren ganzer Zeilen der gleichen Weise wie Ihre erste Zelle. Wählen Sie die gesamte Tabelle, mit der A1 Zelle aktiviert, und legen Sie die bedingte Formatierung mit der Bedingung, definiert durch die folgende Formel:
```
COUNTIF($A:$A, $A1) > 3
```
Ändern 3 um jede andere Konstante, wenn nötig. E. g. über 1 gilt das format auf alle doppelte Werte.

$ bevor ein Teil einer Adresse, ist die absolute Adressierung. $A1 ist die Zelle in Spalte A in der gleichen Zeile wie derzeit formatierte Zelle (wie die Nummer der Zeile ist immer noch relativ). Für mehr Informationen über Zell-Adressierung finden Sie unter Über Zell-und Auswahl-Referenzen @ - Excel-support-Seite.

Anwendung das format von VBA

Mithilfe von VBA können Sie die Formatierung so:
```
Selection.Cells(1,1).Activate
Selection.FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _
    Formula1:="COUNTIF($A:$A, $A1) > 3"
Selection.FormatConditions(1).Interior.ColorIndex = 6
```
Verwenden Range("A:A").Select wählen Sie die gesamte erste Spalte, wenn nicht gemacht die Auswahl sonst. Verwenden Selection.FormatConditions.Delete nach der Auswahl des Bereichs zu erhalten, entfernen Sie die bedingte Formatierung zuvor eingestellt. Möchten Sie vielleicht ändern Sie das format, zu. Die Letzte Zeile stellt nur die Schrift-Farbe aus, mit der Nummer 6 in der aktuellen palette. Es ist gelb, in der Standard-palette, die kann man auf MS Graph-VB-Referenz-Seite für die ColorIndex-Eigenschaft.

Finden Sie unter:
- Programmierung von Regeln der bedingten Formatierung von excel - vba @ MSDN-Foren
- Bedingte Formatierung mit VBA
Löschen von nicht formatierten Zeilen

Löschen Sie die Zeilen, die nicht formatiert ist, wird eine Schleife durch die Zellen, get cell Farbe mit cell.DisplayFormat.Interior.ColorIndex - und call - cell.EntireRow.Delete wenn es nicht farbig:
```
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
    With Selection.Cells(i, 1)
        If .DisplayFormat.Interior.ColorIndex <> 6 Then
            .EntireRow.Delete
        End If
    End With
Next i
```
- Aber dies nur prüfen, ob A1 den Wert gibt es drei mal... Was ist, wenn einige andere Wert gibt es 3 mal? Dann denke ich, die OP will diesen anzünden, auch. Nicht zu erwähnen, dass die anderen Zeilen nicht gelöscht...
- Das ist genau der Punkt, warum ich kann es nicht verwenden @Jean-FrançoisCorbett 🙂
- Getan. Geschrieben aus der Spitze von meinem Kopf, nicht mit Excel hier und jetzt.
- +1 es wahrscheinlich funktioniert jetzt
InformationsquelleAutor Palec

Von dem, was ich verstanden habe, du willst, dass ich etwas verändert-code:

Sub mySub10()

    Dim wsTEMP As Worksheet
    Dim wsSPECIAL As Worksheet
    Dim wsTEMPLrow As Long
    Dim i As Integer
    Dim x As Integer
    Dim rng As Range

    Set wsTEMP = ThisWorkbook.Sheets("Temp")
    Set wsSPECIAL = ThisWorkbook.Sheets("Spezial")

    Application.ScreenUpdating = False

    wsTEMPLrow = Worksheets("Temp").Range("A" & Worksheets("Temp").Rows.Count).End(xlUp).Row

    With wsTEMP

      .Columns("A:Q").Sort Key1:=.Range("L1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    For i = wsTEMPLrow To 5 Step -1
        Set rng = Range("A" & i)
        'this checks the two rows before the present one. If this is true, there are at least three rows with the value.
        If Cells(i, 12).Value = Cells(i - 1, 12).Value And Cells(i, 12).Value = Cells(i - 2, 12).Value Then
                'this way the three rows you know have the value will be colored as desired.
                Range("A" & i).EntireRow.Interior.ColorIndex = 6
                Range("A" & i - 1).EntireRow.Interior.ColorIndex = 6
                Range("A" & i - 2).EntireRow.Interior.ColorIndex = 6
        End If
    Next

    For i = wsTEMPLrow To 2 Step -1
        Set rng = Range("A" & i)
        If rng.Interior.ColorIndex <> 6 Then
            rng.EntireRow.Delete
        End If
    Next

    End With

End Sub

InformationsquelleAutor brWHigino

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

COUNTIF zählt die Anzahl der vorkommen

Bedingte Formatierung

Anwendung das format von VBA

Löschen von nicht formatierten Zeilen

`COUNTIF` zählt die Anzahl der vorkommen