Per VBA Zeilenumbruch entfernen?

Hallo. Ich schreibe grade ein Makro und hänge ein steller fest. Ich muss in einer Zeile in jeder Zelle den manuellen Zeilenumbruch entfernen. Bisher bin ich soweit gekommen

lzeile = Cells(Rows.Count, 1).End(xlUp).Rows.Row For t = 1 To lzeile

Cells(t, 2).Replace Chr(10)

Next t

So läuft es zwar durch aber die Zeilenumbrüche werden auch nicht entfernt. Hat jemand einen Tip was ich an meinem Code ändern muss?

6 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

eddi0102

22.10.2016, 11:10

Manuell mit der Ersetzen-Funktion: Alt + 010

Wenn man dies als Makro aufzeichnet erhält man:

Cells.Replace What:="" & Chr(10) & "", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

dkilli

21.10.2016, 19:52

Drücke Strg + H Dann öffnet sich ein Dialogfenster. Bei Suchen nach gibst du Alt + 010 ein. Dann wirst du zwar nichts sehen. Macht aber nichts. Bei Ersetzen durch kannst du z. B. ein Leerzeichen eingeben. Statt ein Leerzeichen kannst du jedes Zeichen oder Text eingeben

Iamiam

21.10.2016, 20:38

Habe das eben nachvollzogen: Alt(als Taste, nicht als Text!)010

ersetzen durch Beliebiges

es geht tatsächlich doch!

Oubyi, UserMod Light

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

21.10.2016, 17:27

Komischerweise läuft das bei mir SO überhaupt nicht, weil bei Replace ein Argument fehlt und das auch sofort moniert wird!?

Aber auch mit dem zweiten Argument wird es so nicht akzeptiert, sondern will eine Zuweisung.
Interessanter Weise klappt es bei im Test mir SO:

Dim Text As String
Text = Range("A1").Replace(Chr(10), "")

Also einfach, indem ich es einer Variablen zuweise, OHNE die hinterher nochmal zu benutzen. In dem Moment, in dem Text das Replace zugewiesen wird, verschwindet der Umbruch in A1 automatisch, ohne dass ich Text zurückschreibe. Eigentlich müsste es bei Dir dann so klappen:

Dim Text as String
Text = Cells(t, 2).Replace(Chr(10), "")

Klappt es?

P.S.:
Falls es immer noch nicht klapp, stelle sicher, dass der Umbruch wirklich Chr(10) ist. Ich meine, da gibt es noch einen anderen. Mit der Formel =CODE() bekommst Du den heraus.

Woher ich das weiß:Berufserfahrung – IT-Administrator (i.R.)

Iamiam

21.10.2016, 21:29

Bei mir ist da noch ein interessantes Phänomen aufgetreten. Ich wollte das in eine Zelle drunter einfügen mit diesen Befehlen:

Text = ActiveCell.Replace(Chr(10), " ")
ActiveCell.Offset(1, 0).Value = Text

Ergebnis: WAHR !!! (in der Zelle, sonst nichts!)
Auch ohne Offset in derselben Zelle!
Ich schaffs nicht, den Inhalt von Test i'wo anders einzufügen! (vllt in ein Textfeld, hab ich nicht probiert)

Oubyi, UserMod Light

21.10.2016, 21:49

@Iamiam

War bei mir auch so.
Offensichtlich wird das nicht als Zuweisung verstanden, sondern als Abfrage ob beide Strings gleich sind und das sind sie natürlich.
Auch "Set Text" (aus Verzweiflung) funktioniert da nicht.

Ninombre

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

21.10.2016, 20:10

nachdem du bestätigt hast, dass die Umbrüche von alt+enter kommen, sollte es doch mit dem Replace von chr(10) klappen

For i = 1 To 2
Cells(i, 1).Value = Replace(Cells(i, 1).Value, Chr(10), "")
Next

ist mindestens bei meinen Versuchen erfolgreich

DeeDee07

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

21.10.2016, 16:13

Muss es ein Makro sein?

Du kannst einfach links auf den Zeilenkopf rechtsklicken, Zellen formatieren, Ausrichtung, Haken bei Zeilenumbruch entfernen.

Jasteni

Fragesteller

21.10.2016, 16:34

Hallo. Das funktioniert leider so nicht. Dann verschwindet der Zeilenumbruch nicht. Und ja es muss Makro sein. Es sind ungefähr 2 mio. Zellen in ca 1000 Tabellen^^

Jasteni

Fragesteller

21.10.2016, 16:37

@Jasteni

Es handelt sich ja um einen Manuellen Umbruch durch "Alt" und "Enter".

DeeDee07

21.10.2016, 16:44

@Jasteni

Also ich habe mit Alt+Enter Zeilenumbrüche erzeugt, welche sich nach beschriebener Methode entfernen ließen. Wobei ich natürlich nicht sicher sein kann, dass das in allen Excel-Versionen so gehandhabt wird, jedoch gehe ich davon aus.

Falls du mit Tabellen Tabellenblätter meinst, kannst du alle markieren (erstes Blatt anklicken, mit gedrückter shift-taste letztes Blatt anklicken), die ganze Tabelle markieren (oben links) und dann weiter wie beschrieben.

Iamiam

21.10.2016, 19:27

@DeeDee07

Kann es sein, dassd da auch noch Chr(13) (=Wagenrücklauf auf der Schreibmaschine) eine Rolle spielt?

Ansonsten: wie kommt denn der Zeilenumbruch überhaupt zustande? Ist das ein Umbruch innerhalb der Zelle? Da hatte ich mal was, finde das aber vorr. nicht mehr.

Wenn es aber durch Zellenwechsel nach eins drunter ist, dann die Zellen mit & aneinanderhängen, da kommt der Zeilenumbruch nicht mit rüber.

Wenn alle Stricke reissen: Zeichen für Zeichen durchgehen und aneianderhängen, wenn Zeichen >=32.

Oubyi, UserMod Light

21.10.2016, 19:51

@DeeDee07

Also ich habe mit Alt+Enter Zeilenumbrüche erzeugt, welche sich nach beschriebener Methode entfernen ließen.

Das ist FAST richtig.
Ich habe mir das jetzt mal genauer angesehen.
Wenn man mit ALT & ENTER eine Umbruch erzeugt, wird automatisch auch für diese Zelle die Einstellung auf "Zeilenumbruch" gestellt. Wenn man das wieder wegklickt, dann ist der Text auch nicht mehr umgebrochen.
Allerdings ist der manuelle Zeilenumbruch immer noch vorhanden.
In meinem Beispiel erhielt ich dann, auch wenn Zeilenumbruch ausgestellt war und der Text "an einem Stück" erschien mit der Formel:
=CODE(TEIL(B7;6;1))
das Ergebnis:
10
In der Bearbeitungsleiste war der Zeilenumbruch übrigens auch noch zu sehen.
Und sobald ich, z.B. wegen verringerter Spaltenbreite, den Zeilenumbruch wieder einschalte, habe ich den manuellen Umbruch wieder nach dem 6. Zeichen und erst dann die anderen Umbrüche wegen der Spaltenbreite.
Mir ist klar, dass das jetzt Feinheiten sind und ich es nicht "päpstlicher sehen sollte als der Papst", aber erwähnen wollte ich es trotzdem mal.
Sollte natürlich auch keine Kritik sein, nur eine Ergänzung,

DeeDee07

21.10.2016, 19:57

@Oubyi, UserMod Light

Danke für die Ergänzung. Das finde ich schon erwähnenswert, habe es keineswegs als Kritik aufgefasst. (Ich bin ja nicht völlig lernresistent :) )

Guten Tag,

ich suche einen Code für ein Makro,dass in Spalte B unter der letzten beschriebenen Zelle vor einer leeren Zelle (also nicht am kompletten Ende des Sheets) eine Leerzeile einfügt. Habe schon etliche codes ausprobiert, zb

Sub letzte_Zeile()

'letzte benutzte Zelle in Spalte 2 finden DIM Ende As Long With ActiveSheet

Ende = .Cells(Rows.Count, 2).End(xlUp).Row End With

End Sub

Dieser sollte mir eig die letzte beschriebene Anzeigen,es funktioniert aber Garnichts.

Freue mich über Vorschläge. Viele Grüße

...zur Frage

VBA Code Erklärung

Guten Abend zusammen. Könnte mir jemand weiterhelfen bei diesem Code. Was macht er genau in Excel? Also Die einzelnen Schritte die er durchläuft,.

Option Explicit

Public Sub Double_Red()
Dim lngZeile As Long
Dim lngZeilenSprung As Long
Dim strSuchwert As String

lngZeile = Cells(Rows.Count, 4).End(xlUp).Row

  For lngZeilenSprung = lngZeile To 1 Step -1
    strSuchwert = Cells(lngZeilenSprung, 4).Value
      If Application.WorksheetFunction.CountIf(Range(Cells(1, 4), Cells(lngZeile, 4)), strSuchwert) <> 1 Then
        Cells(lngZeilenSprung, 4).Interior.ColorIndex = 3
      End If
  Next lngZeilenSprung

End Sub

...zur Frage

Excel VBA - Zelle auswählen, dann F2

Hallo erneut Excel-Freunde,

mein Titel verrät es bereits. Eine Zelle soll markiert werden, daraufhin soll "F2" und dann "Enter" gedrückt werden.

Mein Makro sieht bisher so aus:

'Ans Ende der Tabelle springen

ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Select

ActiveCell.Offset(1, 0).Select

' Eingabeaufforderung 1 Datum

Eingabe = InputBox("Bitte Datum angeben, wann die Ware eingetroffen ist!" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _ "Bitte schreiben Sie in: tt:mm:jjjj" & Chr(13) & Chr(10) & _ "" & _ "", _ "Zugang [Datum]", ActiveSheet.Range("B1") _ & "_", 7000, 5000)

If StrPtr(Eingabe) = 0 Then ' MsgBox ("Die Schaltfläche Abbrechen wurde gewählt. Der Vorgang wird abgebrochen.") End Else

End If

ActiveCell.Offset(0, 0).Value = Eingabe

Wie man sieht, sucht er sich selbst die letzte Zelle in meiner Tabelle um dort mit einer Eingabeaufforderung das Datum anzugeben.

Nun möchte ich als nächsten Schritt, genau diese Zelle, wo man eben das Datum eingegeben hat mittels dem Makro da oben, ... das er diese Zelle auswählt, "F2" und dann "Enter" drückt.

Alle Versuche dies zu bewerkstelligen, schlugen bisher fehl.

Habt ihr eine Idee?

Viele Grüße,

David :) ... Pixelschubster :D

...zur Frage

Excel VBA eine große Menge leere Zeilen löschen?

Hallo, ich lade eine menge Datensätze in eine Tabelle rein und dabei werden auch die leeren Zeilen zwischendurch mal reinkopiert. Diese möchte ich nun entfernen. Das gelingt mir mittlerweile auch. Das Problem ist, dass es zu lange dauert bei ca. 2800 leere Zeilen, da diese einzeln angeschaut werden und gelöscht. Mein bisheriger Code:

Function loescheLeerenBereichn()

Dim Zelle As Long
Dim LetzteZeile As Long
LetzteZeile = Sheets("Lieferung").UsedRange.SpecialCells(xlCellTypeLastCell).Row
For Zelle = LetzteZeile To 7 Step -1 ' In Range(Cells(7, 4), Cells(LetzteZeile, 4))
If Cells(Zelle, 3) = "" Or Cells(Zelle, 3) = "." Then
Rows(Zelle).EntireRow.Delete Shift:=xlUp 
End If
Next Zelle

End Function

Wie kann man das schneller programmieren?

...zur Frage

Mit Access und VBA einen formatierten Excel-Export durchführen?

Guten Tag, ich hänge mal wieder bei einer Access - Excel Frage fest.

Ich habe ein Access eine Abfrage geschrieben, die mir sortiert eine ID, den zugehörigen Namen und alle dazu zugewiesenen Daten ausgibt.

Da jede Id + name mehrere Daten haben kann, steht das ganzen in mehreren zeilen untereinander. Ich möchte das ganze nun in eine Excel-Datei via Makro exportieren und das ganze so formatieren das Id + name nur einmal drann steht und dann alle Daten.

Das ganze sieht also eig. so aus.

id || name || daten

1 || n1 || daten1

1 || n1 || daten2

1 || n1 || daten3

und soll in der Exel Datei dann so aussehen.

id || name || daten

1 || n1 || daten1

_ || _ || daten2

_ || _ || daten3

_ bedeutet das dort kein Eintrag in der Zelle mehr steht. Ich habe das bisher so gemacht, das ich Die Daten aus Access händisch in eine Excel kopiert habe und dann dort mit einem Makro alles raus sortiert habe. Dim xErsteZeile As Long Dim xZeile As Long

        xErsteZeile = Cells(Rows.Count, 1).End(xlUp).Row
    
        For xZeile = xErsteZeile To 1 Step -1
          If Application.WorksheetFunction.CountIf(Columns(1), Cells(xZeile, 1).Value) > 1 Then
                Range("A" & xZeile).Select
                Selection.Delete Shift:=xlUp
                Range("B" & xZeile).Select
                Selection.Delete Shift:=xlUp
          End If
        Next

Ich würde das ganze jetzt aber über Access machen da es ja der Ausgangspunkt ist. Wenn ich ein Export in ACCES anschaue in VBA sieht dieser ja so aus.

    DoCmd.OutputTo acOutputQuery, "jh_01_01_Abfrage", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityPrint

kann ich davor die Daten noch bearbeiten wie in Excel? Und wenn ja kann mir eventuell jemand einen Tipp geben?

Würde mich sehr freuen.

Grüße

Frost.

...zur Frage

Excel Makro sucht nach Variable und löscht Datensatz!?

Hallo liebe Community,

ich bräuchte eure schnelle Hilfe wieder einmal. Ich habe ein schönes Makro, das an sich super funktioniert. Es sucht nach einer Variable und löscht entsprechend alle Zeilen, wenn man es auslöst.

Beispiel es soll B1 suchen und wenn vorhanden, alle 4 Datenreihen in der Tabelle löschen. Leider habe ich das Problem, an dem ich gerade verzweifle, dass Excel nicht nur B1 löscht, sondern wenn vorhanden, auch B10, B11, B12, B13, B14, B15, B16, B17, B18 und B19

An welcher Stelle des Makros kann ich es ändern, dass Excel wirklich nur das sucht und löscht, was auch tatsächlich gesucht wird.

Mein Makro:

Sub B1_weg()
Dim lngZ As Long
For lngZ = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
  If InStr(Cells(lngZ, 4), "B1") > 0 Then
    Cells(lngZ, 1).EntireRow.Delete
  End If
Next
End Sub

Vielen Dank im Voraus!

...zur Frage

Wie kann ich in Excel eine Zeile mit VBA ausblenden und verschieben?

Hallo,

wenn eine 1 in der Zeile L oder M oder N steht, soll die Zeile ausgeblendet und im Blatt "Monat" eingefügt werden.

Was mache ich hier falsch?

Sub Copy()
  Sheets("Offen").Select
  LetzteZeile = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

  For i = 1 To LetzteZeile
    If Cells(i, 12) = 1 Then
      Rows(i & ":" & i).Select
      Selection.Copy
      Sheets("Monat").Select
      LetzteZeileZwei = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
      LetzteZeileZwei = LetzteZeileZwei + 1
      Rows(LetzteZeileZwei & ":" & LetzteZeileZwei).Select
      ActiveSheet.Paste
      Sheets("Offen").Select
      Section.EntireRow.Hidden = True
    End If
  Next i
End Sub

...zur Frage

VBA Excel Makro komplette Zeile löschen,wenn bestimmte Wörter enthalten

 Dim arrSuchbegriff, i&
 arrSuchbegriff = Array("Suchbegriff1", "Suchbegriff2", "", "SB3")

 For i = LBound(arrSuchbegriff) To UBound(arrSuchbegriff)
     ActiveSheet.Columns("A").Replace arrSuchbegriff(i), "###@@@###", xlPart, MatchCase:=False
 Next i
 ActiveSheet.Columns("A").AutoFilter Field:=1, Criteria1:="*###@@@###*"
 ActiveSheet.Cells.Delete

Das Makro klappt, allerdings löscht es mir nur die Zelle in der der Begriff enthalten ist, und nicht die komplette Zeile. Kann jemand helfen? Viele Grüße

...zur Frage

Excel, Userform: Datum aus Textbox wird in der Zelle nicht als Datum erkannt?

Ich habe folgenden Code:

Private Sub Fertig_Click()

Dim last As Integer

last = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row + 1

Tabelle9.Cells(last, 1) = Format(Eingabe.Antragsdatum.Value, "dd.mm.yyyy")

End Sub

In der Zelle nachher wird es nicht als Datum erkannt.
Muss es aber als Datum sortieren können.

Ich habe schon viele Foreneinträge zu dem Thema gelesen, doch nichts davon funktioniert bei mir.

...zur Frage

Excel: Verbessertes Logging mit VBA Makro?

Hallo Zusammen

ich verwende im Moment ein kurzes Makro, um Änderungen in einem Tabellenblatt zu loggen.

Es registriert aber nur einzelne Änderungen, nicht multi-selektion oder Löschen von Spalten. Kann ich das mit separaten Privaten Subs übernehmen?

Und kann ich aufnehmen, was die Target- und "PreVal"-Adresse war (Offset 4+5)

Code:

Dim PreVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
PreVal = Target.Value
 
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  
    On Error GoTo 0
    
    If Target.Value <> PreVal Then
        With Sheets(2).Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0) = Now
            .Offset(1, 1) = PreVal
            .Offset(1, 2) = Target.Value
            .Offset(1, 3) = Environ("Username")
        End With
    End If
 
End Sub

...zur Frage

Excel VBA - Wert auslesen?

Mein Problem:

Mit dem ersten Makro wird ein Wert in Zelle A1 geschrieben.

In einem weiteren Makro will ich nun diesen Wert auslesen mit Worksheets("Blatt1").Cells(1, 1).Value = i. Wenn ich das mache steht danach in Zelle A1 aber eine 0 und nicht mehr der vorige Wert. Was ist falsch?

...zur Frage

Tabellen mit vba nebeneinander anstatt untereinander anordnen?

Hallo,

ich habe bereits meine Excel-Arbeitsmappe bestehend aus 250 Seiten mit jeweils 2Spalten und 250 Zeilen via vba zusammengeführt.

Allerdings wird dabei alles untereinander dargestellt und nicht nebeneinander. Kann man es so machen, dass sobald ein Worksheet abgearbeitet wurde und angezeigt wird, das nächste daneben in der Spalte beginnen lässt und immer so weiter.

Gibt es Möglichkeiten dort eine Art „Cut“ zu machen?

Sub Konsolidieren()
Dim Wks As Worksheet
Dim Bereich As Range
Dim strLC As String
Dim i As Integer
Set Wks = Worksheets.Add
Wks.Name = "Gesamt"
For i = 2 To Worksheets.Count
 With Worksheets(i).UsedRange
 strLC = .Cells(.Rows.Count, .Columns.Count).Address
 Set Bereich = .Range("A1:" & strLC)
 Bereich.Copy Destination:= _
 Wks.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
 End With
Next i
End Sub

...zur Frage

Excel VBA - Darstellung waagerecht-vertikal beim kopieren ändern?

Hallo,

ich habe in einer fertigen Tabelle etwas geändert, um die Eingabe der Daten zu erleichtern. Statt die Daten in der Spalte zu erfassen, werden die Daten jetzt in einer Zeile eingegeben.

Also statt vorher in B10, B11, B12... jetzt in B10, C10, D10...

Die Daten werden mit Hilfe eine Makros in eine andere Tabelle transferiert und waagerecht eingefügt.

Da ich aber die Eingabeart verändert habe, werden die Daten jetzt untereinander in die Tabelle eingefügt.

Ich weiß, dass das mit "Application.Transpose" zu tun hat.

Nun weiss ich aber nicht wie ich die Zeile umbenennen muss, damit die waagerechte Darstellung übernommen wird.

Wie ändere ich die Zeile, damit die angesprochenen Werte waagerecht in die Zeile kopiert werden?

Hier der komplette Code:

Sub transfer_werte()
    Dim rngKunde    As Excel.Range
    Dim rngReklam   As Excel.Range
    Dim rngArtikel   As Excel.Range

     Set rngKunde = Worksheets("Eingaben").Range("C5:C10")
    Set rngReklam = Worksheets("Eingaben").Range("D5:D9")
    Set rngArtikel = Worksheets("Eingaben").Range("C14:L14")
   
        With Worksheets("Liste")
          With .Cells(.Rows.Count, 2).End(xlUp)
               .Offset(1, 0).Resize(rngKunde.Columns.Count, rngKunde.Rows.Count).Value = Application.Transpose(rngKunde.Value)
        End With
    End With
   
    With Worksheets("Liste")
        With .Cells(.Rows.Count, 8).End(xlUp)
             .Offset(1, 0).Resize(rngReklam.Columns.Count, rngReklam.Rows.Count).Value = Application.Transpose(rngReklam.Value)
        End With
    End With
  
   With Worksheets("Liste")
        With .Cells(.Rows.Count, 14).End(xlUp)
             .Offset(1, 0).Resize(rngArtikel.Columns.Count, rngArtikel.Rows.Count).Value = Application.Transpose(rngArtikel.Value)
        End With
    End With
      
End Sub

...zur Frage

Excel VBA - Spalte von Unten nach Oben durchgehen?

Servus Leute, ich hätte eine kleine Frage zu VBA:

Wie schaffe ich es, dass mein Programm eine bestimmte Zeile von unten nach oben durchläuft.

Hier meine Variante für das durchlaufen von Oben nach Unten

maxRow2 = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp).row

For row2 = 1 To maxRow2
Do something     
Next row2

Wie muss ich das abändern, damit die Spalte von Unten nach Oben durchgelaufen wird?

...zur Frage

Was möchtest Du wissen?

Deine Frage stellen