Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.06.2024 22:07:02
25.06.2024 21:01:55
25.06.2024 19:21:44
Anzeige
Archiv - Navigation
1508to1512
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
GLÄTTEN für gesamte Arbeitsmappe
15.08.2016 08:48:12
Marcel
Hallo zusammen,
ich habe eine sehr große Tabelle (7000 Zeilen und Spalten bis ED) bei der ich gerne die Funktion GLÄTTEN für sämtliche Zellen der Arbeitsmappe durchführen möchte.
Gibt es da einen Weg, dies nicht manuell Spaltenweise machen zu müssen?
Danke und Grüße

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: GLÄTTEN für gesamte Arbeitsmappe
15.08.2016 10:09:46
Marcel
Hallo Dieter,
ja, genau das Suche ich - danke-
Nur: Ich habe mehrere Arbeitsmappen die ähnlich groß sind in der Datei. Gibt es eine Möglichkeit, das im Thread zuerst erwähnte Makro über die gesamte Datei laufen zu lassen?
Danke und Grüße
Trim
15.08.2016 10:18:15
snb

Sub M_snb()
for each sh in sheets
sh.usedrange.name="snb_000"
[snb_000]=[trim(snb_000)]
next
End Sub 

Anzeige
AW: Trim
15.08.2016 11:21:14
Marcel
Ohje, ich kenne mich doch mit VBA nicht wirklich aus :(
Ich würde jetzt Folgendes machen: über Entwicklertools -> Visual Basic -> und dann?
Unten der Code aus dem anderen Thread - wo füge ich denn den Code von "snb" ein?
Danke und Grüße
Sub TrimCells()
Dim objSh As Worksheet
Dim rng As Range, rngR As Range
Dim lngCalc As Long
On Error GoTo ErrExit
With Application
.EnableEvents = False
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each objSh In ThisWorkbook.Worksheets
On Error Resume Next
Set rngR = objSh.UsedRange.SpecialCells(xlCellTypeConstants)
Err.Clear
On Error GoTo ErrExit
If Not rngR Is Nothing Then
For Each rng In rngR
rng = Trim(rng)
Next
End If
Next
ErrExit:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = lngCalc
End With
Set rng = Nothing
Set rngR = Nothing
Set objSh = Nothing
End Sub

Anzeige
AW: Trim
15.08.2016 11:47:22
Werner
Hallo Marcel,
also ehrlich gesagt verstehe ich jetzt das Problem nicht. Du willst das Glätten für sämtliche Blätter deiner Mappe ausführen. Genau das macht doch der Code den du hier eingestellt hast. Hast du den Code eigentlich mal laufen lassen?
In dieser Codezeile läuft er in einer For Next Schleife über alle Blätter der Mappe.
For Each objSh In ThisWorkbook.Worksheets
Gruß Werner
AW: Trim
17.08.2016 09:31:05
Marcel
Hallo Werner,
seltsamerweise macht er was ich will in meiner neu erstellten Test Datei.
In meiner "großen" Datei passiert rein gar nichts. Leider kann ich Sie aufgrund sensibler Daten nicht hochladen. Auch wenn ich einzelne Arbeitsblätter aus der "großen" Datei herauskopiere funktioniert der Code.
Hast Du eine Idee woran es liegen könnte, weshalb es nicht funktionieren könnte?
Danke und Grüße
Marcel
Anzeige
AW: Trim
15.08.2016 11:33:58
Marcel
Ohje, ich kenne mich doch mit VBA nicht wirklich aus :(
Ich würde jetzt Folgendes machen: über Entwicklertools -> Visual Basic -> und dann?
Unten der Code aus dem anderen Thread - wo füge ich denn den Code von "snb" ein?
Danke und Grüße
Sub TrimCells()
Dim objSh As Worksheet
Dim rng As Range, rngR As Range
Dim lngCalc As Long
On Error GoTo ErrExit
With Application
.EnableEvents = False
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each objSh In ThisWorkbook.Worksheets
On Error Resume Next
Set rngR = objSh.UsedRange.SpecialCells(xlCellTypeConstants)
Err.Clear
On Error GoTo ErrExit
If Not rngR Is Nothing Then
For Each rng In rngR
rng = Trim(rng)
Next
End If
Next
ErrExit:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = lngCalc
End With
Set rng = Nothing
Set rngR = Nothing
Set objSh = Nothing
End Sub

Anzeige
AW: Trim
15.08.2016 11:52:51
Dieter(Drummer)
Hallo Marcel,
Sorry, das ich mich jetzt erst melde, war unterwegs.
Du brauchts nur noch den Code von snb. Diesen Code in ein Modul einfügen. Dann das Makro aufrufen und die ganze Arbeitsmappe ist getrimmt. Hab es probiert und funktioniert prima.
Gruß, Dieter(Drummer)
AW: Trim
16.08.2016 13:30:06
Marcel
Hallo zusammen,
nur den Code unten?
Wenn ich es wie folgt einfüge funktioniert es nicht (es werden Zellen gelöscht / überschrieben).
Stimmt mein Weg eigentlich?
Entwicklertools -> Visual Basic -> Doppelklick auf "Diese Arbeitsmappe -> rechts den Code einfügen -> "Play Button" drücken
Danke und Grüße
 Sub M_snb()
for each sh in sheets
sh.usedrange.name="snb_000"
[snb_000]=[trim(snb_000)]
next
End Sub 

Anzeige
AW: Trim
16.08.2016 13:47:35
Dieter(Drummer)
Hallo Marcel,
der Code gehört in ein Modul!
Rechte Maustaste auf Tabellenblatt Register, Code anzeigen, Microsoft Excel, Einfügen "Modul.
Dann siehts du links Module/Modul1. Modul1 anklicken und Code im rechten Fenster einfügen.
Gruß, Dieter(Drummer)
AW: Trim
16.08.2016 16:51:37
Marcel
Hey Dieter,
also irgendwie stell´ ich mich besonders dämlich an.
Ich habe den Code jetzt in das Modul 1 kopiert. Als xlsm Datei gespeichert und sobald ich den "Play-Button" drücke zerhaut es wieder die komplette Datei indem Texte wild eingefügt und gelöscht werden.
AW: Trim
16.08.2016 18:43:50
Dieter(Drummer)
Hallo Marcel,
hast du die Möglichkeit, mir deine Datei (evtl abgespeckte Version) mal über das Forum zu senden? Ich sehe gerne mal danach, wo es evtl. dran liegt, soweit ich es beurteilen kann.
Gruß, Dieter(Drummer)
Anzeige
AW: Trim
17.08.2016 09:33:55
Marcel
Hallo Dieter,
seltsamerweise macht er mit dem längeren Code von oben (nicht der snb) was ich will in meiner neu erstellten Test Datei.
In meiner "großen" Datei passiert rein gar nichts. Leider kann ich Sie aufgrund sensibler Daten nicht hochladen. Auch wenn ich einzelne Arbeitsblätter aus der "großen" Datei herauskopiere funktioniert der Code.
Hast Du eine Idee woran es liegen könnte, weshalb es nicht funktionieren könnte?
Danke und Grüße
Marcel
AW: Habe gerade nochmal getestet ...
16.08.2016 18:58:41
Dieter(Drummer)
... Marcel. Jetzt funktioniert es bei mir auch nicht mehr. Sorry, ich kann da nicht weiter helfen.
Gruß, Dieter(Drummer)
Anzeige
AW: Noch besser erklärt ...
16.08.2016 13:56:19
Dieter(Drummer)
... wie ein Modul eingefügt wird:
https://www.akademie.de/wissen/vba-lernen-excel-2007-variable-konstante-anweisungen/eigene-module
Gruß, Dieter(Drummer)
AW: GLÄTTEN für gesamte Arbeitsmappe
16.08.2016 18:52:43
Daniel
Hi
ohne VBA kannst du das für eine Tabelle so machen:
1. ermittle im ausgangsblatt (Tabelle1) die letzte benutze Zelle. Klicke hierzu STRG+ENDE und merke dir die Zelladresse (z.B ED7000)
2. lege ein neues leeres Tabellenblatt an (Tabelle2)
3. gehe im neuen Tabellenblatt in das Namensfeld (links oben, vor der Bearbeitungszeile) und gebe dort: A1:ED7000 ein (dh die Adresse aller benutzen Zellen auf Tabelle1)
jetzt sollten diese Zellen alle markiert sein.
4. gebe jetzt die Formel: =Glätten(Tabelle1!A1) ein und schließe die Eingabe mit STRG+ENTER ab.
das übernimmt die Formel in alle markierten Zellen.
5. kopiere alle markierten Zellen und füge sie in Tabelle1 Zelle A1 als Wert ein.
6. lösche die Tabelle2 wieder.
Gruß Daniel
Anzeige
AW: GLÄTTEN für gesamte Arbeitsmappe
17.08.2016 09:16:24
Marcel
Hi Daniel,
danke aber ich benötige es für eine Excel-Datei mit ca. 10 Arbeitsmappen. Da wäre Deine Methode zu aufwendig.
Grüße
Marcel
AW: GLÄTTEN für gesamte Arbeitsmappe
17.08.2016 16:41:33
Dieter(Drummer)
Hallo Marcel,
evtl. kannst du mit diesem Code von Rudi Maintaire (aus diesem Formum) etwas anfangen (gehört in ein Modul). Hier werden alle Leerzeichen und das Sonderzeichen 160 im ganzen Tabellenblatt (wenn alle Zellen markiert wurden) entnfernt und das geht sehr schnell. Hinweis: Wenn alle Zellen markiert werden sollen, dann nach Abfrage OBEN LINKS, zwischen "A" und "1" klicken, so wird die ganze Tabelle markiert. Evtl. können die Spezialisten dir dieses Makro so anpassen, dass durch die ganze Arbeitsmappe der Code durchläuft.
Gruß, Dieter(Drummer)
PS Ich hoffe Rudi hat nicht da´gegen, dass ich seinen Code hier weiter veröffentliche.
Makro aus Herber Forum: von Rudi Maintaire am 26.02.2016 11:39:50
Sub LeerzeichenUnd160Raus()
Dim r As Range
On Error Resume Next
Set r = Application.InputBox(prompt:="Bitte Bereich markieren", Type:=8)
On Error GoTo 0
If Not r Is Nothing Then
r.Replace Chr(32), "", xlPart
r.Replace Chr(160), "", xlPart
End If
End Sub

Anzeige
Anpassungen
17.08.2016 19:24:33
Michael
Hi zusammen,
das Makro von snb rennt bei mir auch nicht; ich habe mir die andere Variante angesehen:
Sub TrimCells()
Dim objSh As Worksheet
Dim rng As Range, rngR As Range
Dim lngCalc As Long
On Error GoTo ErrExit
With Application
.EnableEvents = False
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each objSh In ThisWorkbook.Worksheets
On Error Resume Next
Set rngR = objSh.UsedRange.SpecialCells(xlCellTypeConstants)
Err.Clear
On Error GoTo ErrExit
If Not rngR Is Nothing Then
For Each rng In rngR
rng = Trim(rng)
Next
End If
Next
ErrExit:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = lngCalc
End With
Set rng = Nothing
Set rngR = Nothing
Set objSh = Nothing
End Sub
Die ist wiederum insofern fehlerhaft, da sie NIE vorzeitig beendet wird, d.h. die Anweisung
On Error GoTo ErrExit
wird durch das Err.Clear in der vorhergehenden Zeile außer Kraft gesetzt.
Abhilfe:
Option Explicit
Sub TrimCells()
Dim objSh As Worksheet
Dim rng As Range, rngR As Range
Dim lngCalc As Long
Dim fehler As Long ' ******
On Error GoTo ErrExit
With Application
.EnableEvents = False
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each objSh In ThisWorkbook.Worksheets
On Error Resume Next
Set rngR = objSh.UsedRange.SpecialCells(xlCellTypeConstants)
fehler = Err.Number
On Error GoTo ErrExit
If fehler = 0 Then
For Each rng In rngR
rng = Trim(rng)
Next
Else
MsgBox "Fehler " & fehler & " Blatt " & objSh.Name
End If
Next
ErrExit:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = lngCalc
End With
Set rng = Nothing
Set rngR = Nothing
Set objSh = Nothing
End Sub

Durch die MsgBox siehst Du, auf welchem Blatt ein "Fehler" auftritt. Letzterer kann aber auch schlicht bedeuten, daß das Blatt leer ist bzw. keine WERTE vorhanden sind.
VIELLEICHT funktioniert der Code nämlich nur scheinbar nicht bei Deiner Original-Datei, dann nämlich, wenn dort NUR Formeln und keine Werte vorhanden sind.
Wenn Du führende Leerzeichen etwa durch =" "&"Bla" erzeugst, können sie nicht ohne Weiteres durch dieses Makro entfernt werden: dann müßte man nämlich die Formeln durch Werte ersetzen.
Schöne Grüße,
Michael

29 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige