Microsoft Excel

Herbers Excel/VBA-Archiv

for each mit select case ersetzen - fehler

Betrifft: for each mit select case ersetzen - fehler von: Alex
Geschrieben am: 08.03.2016 15:51:49

Hallo,

per VBA sollen bei ca. 600 Zeilen in 16 Gruppen gruppiert werde.
1 Gruppe = z.b. alle Felder mit "1." vorne oder "2.".
Ich denke mal per Select Case ist die Laufzeit deutlich kürzer als mit for each...
oder dauert gruppieren allg. so lange?

Code:

Sub gruppieren()


Dim rngZelle As Range

For Each rngZelle In Worksheets("PM").UsedRange
      If Left(rngZelle.Value, 2) = "1." Then
         rngZelle.Select
         Selection.Rows.Group
         Selection.Rows.Hidden = True
           ElseIf Left(rngZelle.Value, 2) = "2." Then
         rngZelle.Select
         Selection.Rows.Group
         Selection.Rows.Hidden = True
          ElseIf Left(rngZelle.Value, 2) = "3." Then
         rngZelle.Select
         Selection.Rows.Group
         Selection.Rows.Hidden = True
          ElseIf Left(rngZelle.Value, 2) = "4." Then
         rngZelle.Select
         Selection.Rows.Group
         Selection.Rows.Hidden = True
end if 
next rngzelle


Warum funktioniert folgender Code nicht?
Fehler an der Stelle: Selection.Rows.Group - Die Groupmethode konnte nicht ausgeführt werden
For Each rngZelle In Worksheets("PM").UsedRange
                     Select Case rngZelle.Value
                      Case Left(rngZelle.Value, 2) = "1."
                      rngZelle.Select
                      Selection.Rows.Group
                      Selection.Rows.Hidden = True
                      Case Left(rngZelle.Value, 2) = "2."
                      rngZelle.Select
                      Selection.Rows.Group
                      Selection.Rows.Hidden = True
                      Case Left(rngZelle.Value, 2) = "3."
                      rngZelle.Select
                      Selection.Rows.Group
                      Selection.Rows.Hidden = True
                      Case Left(rngZelle.Value, 2) = "4."
                      rngZelle.Select
                      Selection.Rows.Group
                      Selection.Rows.Hidden = True
                      Case Else

                     End Select

  Next rngZelle
End Sub
Danke für Hilfe!

Gruß Alex

  

Betrifft: AW: for each mit select case ersetzen - fehler von: Piet
Geschrieben am: 08.03.2016 16:34:02

Hallo Alex

ob es mit Case schneller laeuft oder mit If Then weiss ich nicht.
Das kannst du nur herausfinden bei grossen Datenmengen > 10.000

Wenn die Profis das Makro sehen meckern sie sofort über deine .Select Methode
Das ist typisch für Makro Recorder Aufzeichnung. Völlig unnötig und kostet Zeit.

Ist dir von der Logik her mal aufgefallen das du eine klassische Oder Funktion hast?
Ich denke das mein Makro ohne Select, mit Oder Funktion, schneller sein dürfte.

Ob du die .Group Funktion brauchst weiss ich nicht? Dann so: rngZelle.Rows.Group.Hidden = True
Ich habe das Makro nicht getestet, würde mich freuen wenn es so klappt.

mfg Piet

Sub gruppieren()
Dim rngZelle As Range

For Each rngZelle In Worksheets("PM").UsedRange
      If Left(rngZelle.Value, 2) = "1." Or _
         Left(rngZelle.Value, 2) = "2." Or _
         Left(rngZelle.Value, 2) = "3." Or _
         Left(rngZelle.Value, 2) = "4." Then
           rngZelle.Rows.Hidden = True
      End If
Next rngZelle



  

Betrifft: AW: for each mit select case ersetzen - fehler von: Alex
Geschrieben am: 08.03.2016 16:54:06

Hallo Piet,

danke für die Hilfe.
Ja, habe ich jetzt schon öfters gelesen, arbeite sehr viel mit .select und with... ist mein erstes VBA-Projekt und gleich richtig groß...
Wie würde ich es denn z.b. in der For Each Schleife ohne Select machen?

rngZelle.Rows.Group <- Musste ich noch einfügen, dass mit .Group.hidden mochte Excel nicht.

Von der Laufzeit her ergibt es keinen Unterschied zur For each Schleife, hätte ich aber auch gedacht.
Er macht das alles auch schnell, aber bevor er "Gruppiert" hängt er sich auf, macht ewig was und dann zack, fertig...
Schade, das es nicht schneller ist.

Sub gruppieren()
Dim rngZelle As Range

For Each rngZelle In Worksheets("PM").UsedRange
      If Left(rngZelle.Value, 2) = "1." Or _
         Left(rngZelle.Value, 2) = "2." Or _
         Left(rngZelle.Value, 2) = "3." Or _
         Left(rngZelle.Value, 2) = "4." Then
           rngZelle.Rows.Group
           rngZelle.Rows.Hidden = True
      End If
Next rngZelle
Gruß Alex


  

Betrifft: AW: for each mit select case ersetzen - fehler von: Daniel
Geschrieben am: 08.03.2016 17:03:04

Hi

Select Case ist nicht schneller als IF THEN, nur je nach Bedinungsfall übersichtlicher zu programmieren.

in deinem Fall bietet sich das Select Case gerade zu an:

For Each rngZelle In Worksheets("PM").UsedRange
    Select Case Left(rngZelle, 2).Value
        Case "1.", "2.", "3.", "4.", "5."
             rngZelle.Rows.Group
             rngZelle.EntireRow.Hidden = True
        Case Else
    End Select
Next
die Frage ist obs wirklich notwendig ist die Zelle Schleife über alle Zellen des benutzten Bereichs laufen zu lassen.
normalerweise stehen solche Grupperungsrelevanten Daten in einer Spalte so dass es wahrscheinlich ausreichen würde, die Schleife über diese Spalte laufen zu lassen.

Wenn du schon Gruppierst, dann brauchst du nicht jede Zelle gruppierte Zelle einzeln auszublenden, es reicht aus, wenn du dir am Schluss nur die oberste Ebene der Gruppierung anzeigen lässt, dann werden alle gruppierten Zeilen unsichtbar.
das machst du dann mit diesem Code:
ActiveSheet.Outline.ShowLevels RowLevels:=1
Gruß Daniel


  

Betrifft: AW: for each mit select case ersetzen - fehler von: Piet
Geschrieben am: 08.03.2016 18:29:52

Hallo Daniel

schön das du dich mit eingeklinkt hast, manchmal hat man "Knöppe" auf den Augen!
Die Daten 1., 2., 3., 4. werden wahrscheinlich in einer Spalte stehen. Habe ich übersehen.

Dann waere es sinnvoll die letzte Zelle zu suchen um UsedRange zu ersetzen.
xx ist die Spalte (z.B. "O") wo die Werte 1., 2., usw. stehen

lastZell = Range("xx10000").End(xlUp).Row
For Each rngZelle In Worksheets("PM").Range("xx" & LastCell)

Jetzt wird nicht mehr der ganze UsedRange durchsucht, sondern nur 1 Spalte!

mfg Piet


  

Betrifft: AW: for each mit select case ersetzen - fehler von: Daniel
Geschrieben am: 08.03.2016 21:09:25

Hi
Damit durchsuchst du aber nur eine Zelle.

Wenn dann

For each rngZelle in Range("XX1:XX" & LastCell)
Oder
For Each rngZelle in WorkSheets("PM").Usedrange.Columns(1).Cells
Gruß Daniel


  

Betrifft: AW: Danke an Daniel für Aufmerksamkeit von: Piet
Geschrieben am: 08.03.2016 21:39:55

Hi Danke, dummer Flüchtigkeitsfehler
passiert gerne wenn man Makros nicht testet.


  

Betrifft: AW: Danke an Daniel für Aufmerksamkeit von: Alex
Geschrieben am: 09.03.2016 08:56:00

Hallo Piet & Daniel,

danke für die Tipps.
Die Auflistung in Spalte 1. erfolgt so:
Es existieren 16 Tabellenblätter und je nachdem welche Vorauswahl getroffen wird, kopiert er einfach entsprechend aus den Tabellenblättern Spalte A+B in das Blatt untereinander und gruppiert anschließend jedes Tabellenblatt.

For Each rngZelle in WorkSheets("PM").Usedrange.Columns(1).Cells
Habe ich noch übernommen :)

Gruß Alex


  

Betrifft: AW: Danke an Daniel für Aufmerksamkeit von: Alex
Geschrieben am: 09.03.2016 09:00:11

Hey Daniel,

es lag wirklich daran, dass ich jede Zeile einzelnd ausgeblendet habe, die lange Laufzeit.

ActiveSheet.Outline.ShowLevels RowLevels:=1 :: werde ich mir merken :)

DANKE!!

Gruß Alex


 

Beiträge aus den Excel-Beispielen zum Thema "for each mit select case ersetzen - fehler"