Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 18:14:45
VBA - Excell - Kopie und "Diebstahl" Schützen
25.04.2024 17:49:39
Bedingte Formatierung
25.04.2024 16:50:59
2 Zellen in 2 Blättern mit unterschiedlichen Bereichen vergl
25.04.2024 15:03:35
Unterschiedliche Dateien miteinander abgleichen lassen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1820to1824
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
1820to1824
1820to1824
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA beschleunigen ?

VBA beschleunigen ?
18.03.2021 08:58:34
Sebastian
Hallo liebe Community,
ich habe ein kleines Problem :) Ich habe ein schon älteres Script, welches auch wunderbar funktioniert. Allerdings ist es sehr langsam, bzw. dauert es sehr lange bis es seinen Job gemacht hat. Dieser wäre:
Blattschutz aufheben -> In Spalte B checken, wo der letzte Eintrag beginnt ( per "" leer ) -> Ab letzten Eintrag ausblenden -> Blattschutz wieder rein
Kann man das irgendwie, anders lösen ? Oder, beschleunigen ? 
Sub ausblenden()
Dim i As Long, rngBereich As Range, wksX As Worksheet
Application.ScreenUpdating = False
For Each wksX In ActiveWorkbook.Worksheets
With wksX
.Unprotect "250585"
Set rngBereich = .Range("B19:B3502")
For i = rngBereich.Cells(rngBereich.Cells.Count).Row To rngBereich.Cells(1).Row Step -1
If .Cells(i, 2).Value = "" Or IsEmpty(.Cells(i, 2).Value) Then .Rows(i).EntireRow.Hidden = True
Next i
.Protect "250585"
End With
Next wksX
Application.ScreenUpdating = True
End Sub


29
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA beschleunigen ?
18.03.2021 09:17:20
Rudi
Hallo,
teste mal 
Sub ausblenden()
Dim i As Long, rngBereich As Range, wksX As Worksheet
Dim rngHide As Range
Application.ScreenUpdating = False
For Each wksX In ActiveWorkbook.Worksheets
Set rngHide = Nothing
With wksX
.Unprotect "250585"
Set rngBereich = .Range("B19:B3502")
For i = rngBereich.Cells(rngBereich.Cells.Count).Row To rngBereich.Cells(1).Row Step -1
If .Cells(i, 2).Value = "" Or IsEmpty(.Cells(i, 2).Value) Then
If rngHide Is Nothing Then
Set rngHide = .Cells(i, 2)
Else
Set rngHide = Union(rngHide, .Cells(i, 2))
End If
End If
Next i
If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
.Protect "250585"
End With
Next wksX
Application.ScreenUpdating = True
End Sub

Gruß
Rudi

Anzeige
AW: VBA beschleunigen ?
18.03.2021 09:17:57
Nepumuk
Hallo Sebastian,
so schneller?
Public Sub ausblenden()
    Dim rngCell As Range, wksX As Worksheet
    Application.ScreenUpdating = False
    For Each wksX In ActiveWorkbook.Worksheets
        With wksX
            .Unprotect "250585"
            For Each rngCell In .Range("B19:B3502")
                If rngCell.Value = vbNullString Then rngCell.EntireRow.Hidden = True
            Next
            .Protect "250585"
        End With
    Next wksX
    Application.ScreenUpdating = True
End Sub

Gruß
Nepumuk

Anzeige
AW: VBA beschleunigen ?
18.03.2021 09:18:41
Klaus
Hi Sebastian,
ich habe rngBereich auf Spalte Z geändert, in der Annahme die ist leer. Wenn nicht, nimm einfach irgendeine andere leere Spalte für rngBereich.
Das Makro sollte, da Schleifenlos, relativ fix rennen:
Sub ausblenden()
On Error GoTo hell
rngBereich As Range, wksX As Worksheet
Application.ScreenUpdating = False
For Each wksX In ActiveWorkbook.Worksheets
With wksX
.Unprotect "250585"
Set rngBereich = .Range("Z19:Z3502")    'Spalte Z muss frei sein
rngBereich.FormulaR1C1 = "=1/NOT(RC2="""")"
rngBereich.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True
rngBereich.ClearContents
.Protect "250585"
End With
Next wksX
hell:
If Err.Number = 1004 Then Resume Next   'falls keine Zeilen gefunden wurden
Application.ScreenUpdating = True
End Sub
LG,
Klaus M.

Anzeige
AW: VBA beschleunigen ?
18.03.2021 09:29:55
volti
Und noch 'ne Variante...
Code:



[Cc]







	
	
	
	




Sub ausblenden()
  Dim i As Long, wksX As Worksheet

  For Each wksX In ActiveWorkbook.Worksheets

     With wksX
          .Unprotect "250585"
          For i = 3502 To 19 Step -1
              If .Cells(i, 2).Value <> "" Then Exit For
          Next i
          .Range("A" & (i + 1) & ":A" & iMax).EntireRow.Hidden = True
          .Protect "250585"
      End With
  Next wksX
End Sub
_________________________
viele Grüße aus Freigericht 😊
Karl-Heinz


Anzeige
AW: VBA beschleunigen ?
18.03.2021 09:35:44
volti
Sorry, nachträglich "versaut". Nimm dies.
Code:

Sub ausblenden()
  Dim i As Long, wksX As Worksheet, iMax As Long
  For Each wksX In ActiveWorkbook.Worksheets
     With wksX
          .Unprotect "250585"
          iMax = 3502
          For i = iMax To 19 Step -1
              If .Cells(i, 2).Value <> "" Then Exit For
          Next i
          .Range("A" & (i + 1) & ":A" & iMax).EntireRow.Hidden = True
          .Protect "250585"
      End With
  Next wksX
End Sub

_________
viele Grüße
Karl-Heinz


Anzeige
AW: VBA beschleunigen ?
18.03.2021 12:55:10
Sebastian
Hey, sorry, ich konnte mir das jetzt erst angucken und probieren :) Noch mal dickes DANKE ! Bevor ich gucken kann, ob es wirklich schneller ist, hab ich ein anderes Problem. das VBA soll beim öffnen der Excel Datei gestartet werden, dies klappe auch per 
Private Sub Workbook_Open()
Call ausblenden
End Sub
Wunderbar. Nun macht er das aber nicht mehr. Erst wenn ich das VBA manuell starte .... Obwohl ich nix verändert habe.

AW: VBA beschleunigen ?
18.03.2021 13:03:44
volti
Hallo Sebastian,
Was macht er nun nicht mehr?
Das kann ich leider nicht nachvollziehen, warum das beim Aufrufen über Workbook.Open nicht funktionieren sollte.
Viele Grüße
Karl-Heinz

Anzeige
AW: VBA beschleunigen ?
18.03.2021 12:58:05
Sebastian
Also die erste Variante von @volti ist schon mal sehr schnell. aber ich muss erst noch den Fehler finden warum es nicht mehr automatisch startet.

ich behaupte trotzdem ...
18.03.2021 13:03:58
Klaus
... dass meine Variante eine milisekunde schneller ist, da sie komplett ohne Schleife auskommt :-)
Das automatische Starten sollte eigentlich so klappen. Um zu sehen woran es scheitert (Makros deaktiviert?) müsstest du einmal die Tabelle hochladen. Meistens ist es dann nur ein Tippfehler.
LG,
Klaus M.

AW: ich behaupte trotzdem ...
18.03.2021 13:14:16
Sebastian
Ich werde gleich alle Varinaten ausprobieren :) Ich habe eine Excel Tabelle die bis 1500 Zeilen ging, hab diese dann auf 3500 erweitert und seit dem geht es nicht mehr :D Finde den Fehler aber nicht. Kann die Tabelle nicht hochladen, ohne die ganzen Bezüge die aus Netzwerkadressen stammt hat sie keinen Inhalt :)

Anzeige
AW: ich behaupte trotzdem ...
18.03.2021 13:17:49
Sebastian
Selbst wenn ich meine 3500er Erweiterung in die alte Funktionierende Tabelle kopiere klappt es nicht mehr ;D

AW: ich behaupte trotzdem ...
18.03.2021 13:32:07
volti
Hallo Sebastian,
hast Du in den zu prüfenden Zellen in Spalte "B" etwa Formeln drin?
Dann wäre das Feld ja nicht leer und es funktioniert nicht mit dem Ausblenden.
Dann müsstest Du schon auf den Formelrückgabewert (z.B. =0) checken oder die Formeln anpassen:
=Wenn(B100=0;"") o.ä.
viele Grüße
Karl-Heinz

AW: ich behaupte trotzdem ...
18.03.2021 13:36:42
Sebastian
Ja genau, das hatte ich ja geschrieben, die werden per Formal als "" Leer angezeigt. Und ab der letzten Zeile die nicht leer ist muss ALLES danach ausgeblendet werden. Jetzt blendet er nur von der letzten vollen Zelle bis 3502 aus :)

Anzeige
iMax fehlt
18.03.2021 09:37:20
Klaus
Hallo Karl-Heinz,
ich glaube wir drei oben haben alle überlesen, dass "ab der letzten Zeile" ausgeblendet werden soll und nicht mittendrin. Um nur den letzten Bereich auszublenden und nicht "mittendrin" Zeilen mitzunehmen, ist deine Lösung natürlich die eleganteste.
Nur: wo kommt denn iMax her? Die hast du vergessen zu deklarieren, oder?
LG,
Klaus

Beitrag zurück gezogen :-)
18.03.2021 09:38:04
Klaus
.

AW: Beitrag zurück gezogen :-)
18.03.2021 10:05:09
Sebastian
Hey, erst mal vielen Dank für die Mühe :) Und ja es ist natürlich wichtig das nach der letzten gefüllten Zelle in B ausgeblendet wird :) Da ich mich selbst mit der VBA Klamotte nur ganz mies auskenne, kann ich dir die iMax Frage nicht beantworten :D Ja ich dachte man kann das irgendwie hinbekommen das es nicht so lange durchläuft .)

Anzeige
AW: Beitrag zurück gezogen :-)
18.03.2021 10:22:04
Klaus
Sebastian, hast du gesehen dass es auch 4 valide Lösungen gibt, oder hast du nur den letzen Beitrag angesehen?
LG,
Klaus M.

AW: Beitrag zurück gezogen :-)
18.03.2021 11:25:58
volti
Hallo Sebastian,
das mit dem iMax ist im Folgethread von mir ja korrigiert worden...
Das passiert (mir) leider manchmal, wenn man bereits getesteten Code noch mal verändert. :-(
VG KH

AW: Beitrag zurück gezogen :-)
18.03.2021 13:33:30
Sebastian
hey .) Beim Testen viel mir auf das wir uns evtl faslch verstanden haben, bzw ich hab micg flasch ausgedrückt. Das Script muss bis Zeile 3500 checken wo inhalt ist, und dann da abschneiden wo schluss ist. Also wenn 2500 Zeilen Inhalt haben darf nach 2500 nichts mehr kommen :) Also alles danach ausgeblendet sein

AW: VBA beschleunigen ?
18.03.2021 13:43:32
Sebastian
Es sieht gut aus :) Ich glaube ich hab alles :) Es tut mir leid das ich etwas durcheinander geantwortet habe :) Ich teste jetzt mal alles und melde mich :) Danke schon mal an ALLE

AW: VBA beschleunigen ?
18.03.2021 13:58:52
Sebastian
Also die Scripte an sich laufen alle. Nur werden sie nicht beim WORKBOOK OPEN ausgeführt, aber das klappe auch mit meiner alten Datei nicht. Bei der 1500 Zeilen Varainate klappt alles. Verstehe ich nicht wirklich.

AW: VBA beschleunigen ?
18.03.2021 13:54:07
Daniel
HI
schneller mit Autofilter 
Sub ausblenden()
Dim i As Long, rngBereich As Range, wksX As Worksheet
For Each wksX In ActiveWorkbook.Worksheets
With wksX
.Unprotect "250585"
Set rngBereich = .Range("B18:B" & .cells.Specialcells(xlcelltypelastcell).row)
rngBereich.Autofilter Field:=1, criteria1:="", visibledropdown:=false
.Protect "250585"
End With
Next wksX
Application.ScreenUpdating = True
End Sub
gruß Daniel

AW: VBA beschleunigen ?
18.03.2021 13:59:33
Klaus
Hallo Daniel,
das setzt aber vorraus, dass der Autofilter für nichts anderes benötigt wird.
Meine Lösung mit dem #DIVO!-Trick ist genauso schnell wie der Autofilter, lässt diesen aber für andere Zwecke aktiv sein wenn nötig.
LG,
Klaus M.

AW: VBA beschleunigen ?
18.03.2021 14:10:14
Sebastian
Hey schöne Varinate, aber er blendet mir alle Leerzeilen oberhalb von B19 auch aus. Das geht nicht :D

AW: VBA beschleunigen ?
18.03.2021 15:05:42
Daniel
macht der Code normalerweise nicht.
der Autofilter wirkt nur auf den Bereich, den man auch angegeben hat.
Da hast du was falsch gemacht oder nicht richtig umgestetzt.
Oder deine Datei enthält eine Besonderheit, die du uns verschwiegen hast.
Lad die Datei mal hoch, das möchte ich mir genauer ansehen.
ich gehe natürlich davon aus, dass sich unterhalb der Zeile 19 noch daten befinden.
Gruß Daniel

AW: VBA beschleunigen ?
19.03.2021 09:45:42
Sebastian
Leider kann ich die Datei nicht hochladen ? Also nicht hier zumindest ... Aber ja, unterhalb von B19 kommen reichlich Daten :) Verstehe aber auch nicht warum er oberhalb von B19 auch alle Leerzeilen platt macht :)

AW: VBA beschleunigen ?
19.03.2021 09:51:11
Daniel
naja, ohne die Datei und deinen Code zu kennen, kann ich dazu nichts sagen.
Das hochladen einer Datei sollte kein Problem sein, wenn du auf der Seite beschriebenen Punkte beachtest.
wenn dir in der Dateiauswahl keine Exceldateien angezeigt werden, liegt das an deinem Browser, der hier seinen Filter auf Browser-Dateien voreingestllt hat und somit Exceldateien ausblendet. Hier müsstest du auf "alle Dateien" umstellen (unten rechts)
Gruß Daniel

AW: VBA beschleunigen ?
19.03.2021 10:24:50
Sebastian
Die Datei ist 3,5 MB groß :D Das ist das Problem. Das viel größere Problem ist, das er das " Ausblenden " nicht mehr beim öffnen der Arbeitsmappe Startet :( Kann ich dir die Datei irgendwie mal anders zukommen lassen ?

AW: VBA beschleunigen ?
19.03.2021 13:09:58
Daniel
naja, ein Tabellenblatt reicht.
und alles was unterhalb von Zeile 30 steht, kannst du auch löschen.
nur das von dir beschriebene Verhalten des Makros sollte nachstellbar sein.
alles was das nicht beeinflusst, kannst du löschen.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige