Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1488to1492
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
Inhaltsverzeichnis

Wertkopie - Bereich eingrenzen

Wertkopie - Bereich eingrenzen
18.04.2016 12:36:24
Lutz
Hallo,
ich habe einen VBA-Befehl zum Erstellen einer Wertkopie (geht auch alles wunderbar). Nun möchte ich aber den Bereich eingrenzen (bis Spalte AG und max Zeile 10.000).
Aktuell dauert es super lange, da er alle Zellen der Arbeitsmappe durchrechnet scheinbar.
Sub Wertkopie_DBR_DBS_auto()
Dim Bereich As Range
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Endspalte = Selection.Columns(Selection.Columns.Count).Column
Endzeile = Selection.Rows(Selection.Rows.Count).Row
Startspalte = 1
Startzeile = 1
Set Bereich = Range(Cells(Startzeile, Startspalte), Cells(Endzeile, Endspalte))
Calculate
For Each Z In Bereich
On Error Resume Next
If Z.HasFormula Then
Select Case Left(Z.Formula, 4)
Case "=DBR"         ' DBRn - Funktion
Z.Value = Z.Value
Case "=DBS"         ' DBSn - Funktion
Z.Value = Z.Value
Case "=SUB"         ' SUBNM - Funktion
Z.Value = Z.Value
Case "=VIE"         ' VIEW - Funktion
Z.Value = Z.Value
Case "=DIM"         ' DIMNM - Funktion
Z.Value = Z.Value
End Select
End If
Next Z
Calculate
Mldg = "fertig"
MsgBox (Mldg)
End Sub

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wertkopie - Bereich eingrenzen
18.04.2016 12:53:09
ChrisL
Hi Lutz
Nach "Startzeile = 1" folgenden Code einfügen.
If Endspalte > 33 Then Endspalte = 33
If Endzeile > 10000 Then Endzeile = 10000
Das erste "Calculate" ist überflüssig. Das zweite vermutlich auch.
Besser du schaltest die automatische Berechnung am Anfang vom Code aus und am Schluss wieder ein:
Application.Calculation = xlCalculationManual
' Code dazwischen
Application.Calculation = xlCalculationAutomatic
cu
Chris

AW: Wertkopie - Bereich eingrenzen
18.04.2016 13:08:39
Daniel
Hi
eingrenzen würde so gehen:
Endspalte = Selection.Columns(Selection.Columns.Count).Column
Endspalte = Worksheetfunction.Min(Endspalte, 33)
Endzeile = Selection.Rows(Selection.Rows.Count).Row
Endzeile = Worksheetfunction.Min(EndZeile, 10000)
wenn dein Tabelle keine Leerzeilen und Leerspalten aufweist, könntest du den Bereich mit:
Set Bereich = Range("A1").CurrentRegion

zuweisen, was sich dann nur auf die Tatsächlich mit einem Wert oder einer Formel befüllten Zellen bezieht, während die .lastCell auch Formatierungen berücksichtigt (was dann schnell den Zellbereich sehr gross werden lassen kann, wenn man ganze Zeilen oder Spalten formatiert)
mit
For Each Z in Bereich.SpecialCells(xlcelltypeformulas) 

läuft die Schleife nur über alle Zellen mit Formel, das sollten ein paar weniger sein als alle und die Prüfung mit .HasFormula kann entfallen.
da bei dir im Select Case sowieso immer nur das gleiche passiert, kannst du das in einen Casefall zusammenfassen.
Wird zwar nicht schneller, sieht aber besser aus:
Select Case Left(Z.Formula, 4)
Case "=DBR", "=DBS", "=SUB", "=VIE", "=DIM"
Z.Value = Z.Value
End Select
ist es richtig, dass es auf dem Blatt Formeln gibt, welche als Formel erhalten bleiben sollen?
wenn nein, dann könntest du auch den ganzen Bereich mit einem Befehl umwandeln.
desweiteren solltest du dem Code ein:
Application.Calculation = xlcalculationmanual
Application.ScreenUpdating = False
voranstellen, damit diese beiden Aktionen dann nicht bei jedem Schleifendurchlauf ausgeführt werden.
am Schluss dann wieder die Application.Calculation auf den Ursprungswert zurückstellen, die Bildschirmaktualisierung wir automatisch bei Makroende wieder eingeschaltet.
Gruß Daniel
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige