Thema: VBA: Führendes Leerzeichen löschen

Hallo Excelfreunde,
in der Recherce bin ich zwar fündig geworden, nur klappt es nicht, was ich erreichen möchte.

Sub leerzeichen_links_weg()
Dim laR As Long, i As Long
laR = cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To laR
LTrim (cells(i, 2).Value)
Next i
End Sub

Die Anzahl der Einträge wird in Spalte A ermittelt und das führende Leerzeichen soll ich Spalte B entfernt werden.
Was ist hier noch falsch an dem VBA, denn das führende Leerzeichen wird nicht wie gewünscht entfernt?
Gruß Manfred

Hi
du musst den Funktionswert auch in die Zelle schreiben:

cells(i, 2).Value = LTrim(Cells(i, 2).Value)

Gruß Daniel

Hallo Daniel,
danke das funktioniert jetzt, dauert bei ca. 100.000 Datensäten nur recht lange. Gibt es vielleicht auch eine Möglichkeit über den Befehl ersetzen? Schwierigkeit dabei ist scheinbar, das nur das führende Leerzeichen ersetzt werden soll.
Gruß Manfred

Hi!
Klar, 100.000*2 Zellzugriffe kosten Zeit!
Schreibe in eine Hilfsspalte in den gesamten Bereich die Formel =GLÄTTEN(B1)
(nicht per Schleife!)

Range("PQS2:PQS100000").Formula = "=TRIM(B2)"

Kopiere den Bereich und füge ihn als Wert in B ein und lösche die Hilfsspalte.
Gruß Ralf

wobei man beachten muss das Glätten nicht nur die führenden Leerzeichen löscht (nur das führende war die Anforderung), sondern auch die folgenden Leerzeichen und alle mehrfach aufeinanderfolgenden Leerzeichen innerhalb des Textes.
Die Funktion LTrim, welche nur die führenden Leerzeichen entfernt, gibt es in Excel nicht.
wenn man mit Formeln arbeiten will, müsste man sich so behelfen:
a) wenn es immer nur genau ein oder kein führendes Leerzeichen geben kann, mit dieser Formel:

=Wenn(Links(B2;1)=" ";Teil(B2;2;9999);B2) 

also Code dann:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=IF(Left(RC2,1)="" "",Mid(RC2,2,9999),RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

b) wenn es mehrere führende Leerzeichen geben kann, müsste man das LTrim auch in Excel verfügbar machen.
hierzu schreibt man dann folgenden code in ein allgmeines Modul:

Public Function GlättenLinks(txt as string) as string
GlättenLinks = LTrim(txt)
End Function

dann kann man die Funktion GlättenLinks in Excel wie eine normale Excelfunktion einsetzen und das auch im Code verwenden:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=GlättenLinks(RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

Gruß Daniel

wobei man beachten muss das Glätten nicht nur die führenden Leerzeichen löscht (nur das führende war die Anforderung), sondern auch die folgenden Leerzeichen und alle mehrfach aufeinanderfolgenden Leerzeichen innerhalb des Textes.
Die Funktion LTrim, welche nur die führenden Leerzeichen entfernt, gibt es in Excel nicht.
wenn man mit Formeln arbeiten will, müsste man sich so behelfen:
a) wenn es immer nur genau ein oder kein führendes Leerzeichen geben kann, mit dieser Formel:

=Wenn(Links(B2;1)=" ";Teil(B2;2;9999);B2) 

also Code dann:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=IF(Left(RC2,1)="" "",Mid(RC2,2,9999),RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

b) wenn es mehrere führende Leerzeichen geben kann, müsste man das LTrim auch in Excel verfügbar machen.
hierzu schreibt man dann folgenden code in ein allgmeines Modul:

Public Function GlättenLinks(txt as string) as string
GlättenLinks = LTrim(txt)
End Function

dann kann man die Funktion GlättenLinks in Excel wie eine normale Excelfunktion einsetzen und das auch im Code verwenden:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=GlättenLinks(RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

Gruß Daniel

Hallo Manfred,
deine Anweisung mit LTrim musst du natürlich auch wieder an eine Zelle/Varibale übergeben:

 Cells(i,2) = LTrim(Cells(i,2)

lg Matthias

Hallo Manfred,
du musst bei deinem Ltrim zuvor noch die Zelle angeben, in die das Ergebnis des LTrim eingetragen wird!

Sub leerzeichen_links_weg()
Dim laR As Long, lngI As Long
laR = cells(Rows.Count, 1).End(xlUp).Row
For lngI = 1 To laR
cells(lngI,2).value = LTrim (cells(lngI, 2).Value)
Next lngI
End Sub

Gruß
Jürgen

Hallo,
sollte das Geschwindigkeitsproblem lösen:

Sub leerzeichen_links_weg()
Dim laR As Long, i As Long, vTmp
Application.ScreenUpdating = False
laR = Cells(Rows.Count, 1).End(xlUp).Row
vTmp = Cells(1, 2).Resize(laR)
For i = 1 To laR
vTmp(i, 1) = LTrim(vTmp(i, 1))
Next i
Cells(1, 2).Resize(laR) = vTmp
End Sub

Gruß
Rudi

Hallo Rudi,
vielen Dank, das klappt jetzt wesentlich schneller.
Gruß Manfred

Hallo Excelfreunde,
in der Recherce bin ich zwar fündig geworden, nur klappt es nicht, was ich erreichen möchte.

Sub leerzeichen_links_weg()
Dim laR As Long, i As Long
laR = cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To laR
LTrim (cells(i, 2).Value)
Next i
End Sub

Die Anzahl der Einträge wird in Spalte A ermittelt und das führende Leerzeichen soll ich Spalte B entfernt werden.
Was ist hier noch falsch an dem VBA, denn das führende Leerzeichen wird nicht wie gewünscht entfernt?
Gruß Manfred

Hi
du musst den Funktionswert auch in die Zelle schreiben:

cells(i, 2).Value = LTrim(Cells(i, 2).Value)

Gruß Daniel

Hallo Daniel,
danke das funktioniert jetzt, dauert bei ca. 100.000 Datensäten nur recht lange. Gibt es vielleicht auch eine Möglichkeit über den Befehl ersetzen? Schwierigkeit dabei ist scheinbar, das nur das führende Leerzeichen ersetzt werden soll.
Gruß Manfred

Hi!
Klar, 100.000*2 Zellzugriffe kosten Zeit!
Schreibe in eine Hilfsspalte in den gesamten Bereich die Formel =GLÄTTEN(B1)
(nicht per Schleife!)

Range("PQS2:PQS100000").Formula = "=TRIM(B2)"

Kopiere den Bereich und füge ihn als Wert in B ein und lösche die Hilfsspalte.
Gruß Ralf

wobei man beachten muss das Glätten nicht nur die führenden Leerzeichen löscht (nur das führende war die Anforderung), sondern auch die folgenden Leerzeichen und alle mehrfach aufeinanderfolgenden Leerzeichen innerhalb des Textes.
Die Funktion LTrim, welche nur die führenden Leerzeichen entfernt, gibt es in Excel nicht.
wenn man mit Formeln arbeiten will, müsste man sich so behelfen:
a) wenn es immer nur genau ein oder kein führendes Leerzeichen geben kann, mit dieser Formel:

=Wenn(Links(B2;1)=" ";Teil(B2;2;9999);B2) 

also Code dann:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=IF(Left(RC2,1)="" "",Mid(RC2,2,9999),RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

b) wenn es mehrere führende Leerzeichen geben kann, müsste man das LTrim auch in Excel verfügbar machen.
hierzu schreibt man dann folgenden code in ein allgmeines Modul:

Public Function GlättenLinks(txt as string) as string
GlättenLinks = LTrim(txt)
End Function

dann kann man die Funktion GlättenLinks in Excel wie eine normale Excelfunktion einsetzen und das auch im Code verwenden:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=GlättenLinks(RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

Gruß Daniel

wobei man beachten muss das Glätten nicht nur die führenden Leerzeichen löscht (nur das führende war die Anforderung), sondern auch die folgenden Leerzeichen und alle mehrfach aufeinanderfolgenden Leerzeichen innerhalb des Textes.
Die Funktion LTrim, welche nur die führenden Leerzeichen entfernt, gibt es in Excel nicht.
wenn man mit Formeln arbeiten will, müsste man sich so behelfen:
a) wenn es immer nur genau ein oder kein führendes Leerzeichen geben kann, mit dieser Formel:

=Wenn(Links(B2;1)=" ";Teil(B2;2;9999);B2) 

also Code dann:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=IF(Left(RC2,1)="" "",Mid(RC2,2,9999),RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

b) wenn es mehrere führende Leerzeichen geben kann, müsste man das LTrim auch in Excel verfügbar machen.
hierzu schreibt man dann folgenden code in ein allgmeines Modul:

Public Function GlättenLinks(txt as string) as string
GlättenLinks = LTrim(txt)
End Function

dann kann man die Funktion GlättenLinks in Excel wie eine normale Excelfunktion einsetzen und das auch im Code verwenden:

With ActiveSheet.usedrange
With .columns(.columns.Count + 1)
.FormulaR1C1 = "=GlättenLinks(RC2)"
.Offset(0, 2 - .column) = .value
.ClearContents
End with
end With

Gruß Daniel

Hallo Manfred,
deine Anweisung mit LTrim musst du natürlich auch wieder an eine Zelle/Varibale übergeben:

 Cells(i,2) = LTrim(Cells(i,2)

lg Matthias

Hallo Manfred,
du musst bei deinem Ltrim zuvor noch die Zelle angeben, in die das Ergebnis des LTrim eingetragen wird!

Sub leerzeichen_links_weg()
Dim laR As Long, lngI As Long
laR = cells(Rows.Count, 1).End(xlUp).Row
For lngI = 1 To laR
cells(lngI,2).value = LTrim (cells(lngI, 2).Value)
Next lngI
End Sub

Gruß
Jürgen

Hallo,
sollte das Geschwindigkeitsproblem lösen:

Sub leerzeichen_links_weg()
Dim laR As Long, i As Long, vTmp
Application.ScreenUpdating = False
laR = Cells(Rows.Count, 1).End(xlUp).Row
vTmp = Cells(1, 2).Resize(laR)
For i = 1 To laR
vTmp(i, 1) = LTrim(vTmp(i, 1))
Next i
Cells(1, 2).Resize(laR) = vTmp
End Sub

Gruß
Rudi

Hallo Rudi,
vielen Dank, das klappt jetzt wesentlich schneller.
Gruß Manfred