Excel 2010 - Leerspalte markieren bis zum Ende der Nachbarspalte

Hallo, Ich muß sehr lange Datensätze (bis 600000 Zeilen) bearbeiten. Zeile 1 enthält immer die Spaltenüberschriften. Dauernd muß ich eine Formel, die ich in Zeile 2 schreibe, in alle darunterliegenden Leerzellen kopieren: So tief, wie die Nachbarspalte links Werte enthält.

Leider geht das wohl nur mit Shift plus Scrollen, was aber nervig, ungenau und zeitaufwendig ist. Die Kurzgriffe STRG-Shift-Ende oder STRG-Shift-PfeilAb versagen, sie gehen nur, wenn Zellen gefüllt sind.

Weiß jemand ein Verfahren, wie es mit wenigen Klicks oder einer Tastenkombination doch geht? VBA sollte es nicht sein.

2 Antworten

Vom Beitragsersteller als hilfreich ausgezeichnet

DeeDee07

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

28.10.2014, 21:29

Da gibt's einen kleinen Trick. Markier die Zelle mit der Formel (in Zeile2) und geh mit der Maus in die rechte untere Ecke der Zelle, als wolltest du die Formel nach unten kopieren. Zieh das Fadenkreuz aber nicht runter, sonder doppelklick drauf. Jetzt wird die Formel nach unten vervollständigt bis zur ersten leeren Zellen der linken Nachbarspalte. Wenn es dort keine Lücken gibt, reicht also ein einziger Doppelklick.

Geooekologe

Beitragsersteller

28.10.2014, 21:40

Super, DeeDee07, hat bestens funktioniert.

Das wenn ich eher gewußt hätte, es hätte mir Jahre erspart :-/

Kriegst natürlich den Stern, herzlichen Dank.

DeeDee07

28.10.2014, 21:40

@Geooekologe

Bitteschön!

Iamiam

05.11.2014, 09:47

@DeeDee07

damit die Alternativmethode bei der anderen steht und von weiteren Usern gefunden werden kann, erlaube ich mir, sie an diese AW anzuhängen.

wenn jemand die gefüllte Spalte rechts hat (geht natürlich links genauso, nur Seitenverkehrt) oder eine kompliziertere Konstellation (Shift ist Umschalt):
Formelzelle kopieren
erste Einfügezelle markieren
Shift-eins nach rechts
Shift-ende unten
Shift-eins nach links
einfügen.
Im Gegensatz zum obigen sieht man hier, ob da Leerzellen sind und die Liste danach wieder weitergeht und kann ggf
Shift-Endeunten
wiederholen
Das geht übrigens auch, wenn die gefüllte Spalte weiter weg ist, egal, was dazwischen steht. Dann lediglich
mehrfach Shift-nachlinks/rechts drücken.

Iamiam

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

29.10.2014, 09:55

für diesen Trick vergeude ich auch mal eine AW., damit Du den * kriegen kannst!
(Die Frager selbst denken an diese Möglichkeit leider nur selten)
(Meine Methode war übrigens erheblich aufwändiger)

DeeDee07

29.10.2014, 22:53

Danke Iamiam!

Ähnliche Beiträge

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?

...zum Beitrag

Excel-VBA: Nächste sichtbare Zeile bearbeiten

Ich muss z.T. große Dateien mit VBA bearbeiten, wobei oftmals mehrer 10.000 Datensätze ausgeblendet sein können. Diese müssen nicht bearbeitet werden.

Bisher habe ich die nächste zu verarbeitende Zeile gesucht, indem ich alle ausgeblendeten Zeilen per Schleife ignoriert habe (If ActiveSheet.Rows(_ExcelRow).EntireRow.Hidden). Dies kann bei 30.000 Sätzen aber schon mal eine ganze Weile dauern. Gibt es stattdessen eine elegantere Methode? Sendkeys("{Down}") funktioniert bei mir nicht, wenn Excel minimiert ist.

...zum Beitrag

VBA Zellen durchsuchen und entsprechend ausblenden/einblenden?

Hallo,

ich möchte gerne einen VBA erstellen mit folgendem Ziel:

Zelle A2 enthält "nein"

Zelle A2 mit "nein" führt zum Ausblenden der Inhalte von Zeilen 5-8. Soweit verstehe ich den Makro.

Jetzt zu meinen Problemn:

Ich lösche Zeile 1 und der o.s. Befehl soll über Zelle A1 ausgeführt werden
Ich füge eine weitere Zeile in Zeile 4 ein und nur die Inhalte aus Zeilen 5-8, was jetzt neuerdings 6-9 ist, wird ausgeblendet. Ich möchte jetzt nicht mehr Zeile 5-8 ausblenden.

Sprich, ich möchte gerne die Range im VBA verschieben, wenn ich im Blatt Zeilen hinzufüge/lösche.

Danke vorab!

...zum Beitrag

Java eine Zeile kommtieren geht blockcommentar geht nicht?

In Java mit der IDE eclipse

Ich markiere mehrere Zeilen, drücke strg + 7 alle Zeilen die ich markiert hatte werden mit // auskommentiert. Drücke ich nochmal strg + 7 verschwinden diese wieder.

Passt soweit

Jetzt soll es die Möglichkeit eines Block-Kommentars geben, dass über

strg + shift + 7 ausgelöst wird. Dies funktioniert bei mir nicht obwohl festgelegt ist:

Hier sieht man, dass unter Add Block Comment strg + shift + 7 steht, was das selbe ist wie Ctrl + shift + /

Ich habe hier alles auf den Defaults gelassen und nichts umgestellt. Drücke ich strg + shift + 7 wird das gleiche ausgeführt, als wenn ich strg + 7 drücke, es erscheint ein einzeiliger Kommentar mit //

Warum funktioniert der Blockkommentar nicht?

MFG

...zum Beitrag

Excel VBA: Werte in jeweils vorgegebene Zelle schreiben

Die VBA-Programmierung ist für mich Neuland und ich bitte um Untersützung bei einer Sache:

Ich möchte Werte eines Arbeitsvorgangs, die in einem Excelsheet "WO-Vorgangsliste" sich befinden, in ein anderes Sheet "WO-Ablaufplan" in die jeweils vorgegebene Zelle schreiben. In der "WO-Vorgangsliste" sind mehrere Vorgänge als Datensätze gelistet. Jeder Vorgang bzw. jeder Datensatz beinhaltet in Spalte 9 als Wert die Zellenposition, wo er hinkopiert werden soll.

Konkret: -für ersten Vorgang: Wert in Zeile 5 Spalte 7 (5,7) soll gemäß (5,9) in "WO-Ablaufplan" in Zelle (Z41S9)

-für zweiten Vorgang: Wert in Zeile 6 Spalte 7 (6,7) soll soll gemäß (6,9) in "WO-Ablaufplan" in Zelle (Z76S17)

-für dritten Vorgang: Wert in Zeile 7 Spalte 7 (7,7) soll soll gemäß (7,9) in "WO-Ablaufplan" in Zelle (Z36S12)

-allgemein: Schleife für: Wert von Zeile i Spalte 7 gemäß hinterlegter Zellenposition aus Zeile i Spalte 9 der"WO-Vorgangsliste" in entsprechende Zelle von "WO-Ablaufplan" (gemäß hinterlegtem Wert aus Zeile i Spalte 9) kopieren.

Ich hoffe, das ist soweit verständlich und freue mich auf eure Unterstützung!

...zum Beitrag

Excel VBA Zellen in ein anderes Tabellenblatt Kopieren wenn Bedingung erfüllt?

Hallo,

ich habe ein problem und habe keine Ahnung von VBA.

Ich habe eine Tabelle in "A" steht der Name in "P" der Kurs und in "Y" ist die Bedingung "ja" oder "nein".

Das war das einzige das ich gefunden habe was auch funktionierte. Allerdings Kopiert es die komplette Zeile.

Sub BedingteKopieZeilen()

Dim Zeile As Long

Dim ZeileMax As Long

Dim n As Long

With Tabelle2

ZeileMax = .UsedRange.Rows.Count

n = 7

For Zeile = 3 To ZeileMax

If .Cells(Zeile, 25).Value = "Kurtaxe" Then

.Rows(Zeile).Copy Destination:=Tabelle10.Rows(n)

n = n + 1

End If

Next Zeile

End With

End Sub

Ich bedanke mich für eure Hilfe

Bernd

...zum Beitrag

VBA for-schleife: Leere Zeilen überspringen?

Hallo! Ich habe mir in Excel mit VBA eine Schleife gebastelt zur Zielwertsuche. Da ich VBA sonst nie brauche möchte ich hierfür nicht erst VBA lernen. Daher hier meine Frage ob mir hier jemand helfen kann. Es geht um folgendes Skript. Dieses macht Probleme wenn zwischen den Zeilen Leerzeilen sind (Fehler 400). Kann man das irgendwie verhindern? Die leeren Zeilen z.B. überspringen?

Vielen Dank!

Sub PreisBerechnung()
Dim IntZeile As Integer
For IntZeile = 4 To 199
Cells(IntZeile, 33).GoalSeek Goal:=2.5, ChangingCell:=Cells(IntZeile, 26)

Next
End Sub

...zum Beitrag

Excel aktive Zeile farblich markieren ohne VBA?

Ich habe laut einer Anleitung auf YouTube

Hier ist der Link

Kurzgefasst wird dort in einem bestimmten Bereich eine Bedingte Formatierung gemacht, mit der Formel:

=$A$1=ZEILE()

Hierzu wird noch ein VBA Code gebraucht:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("$A$4:$Q$10000")) Is Nothing Then
Range("$A$1").Value = Target.Row
End If
End Sub

Resultat:
Die Zeile der aktiven Zelle wird nun farblich markiert.

Frage:
Mir ist nun aufgefallen, dass ja jedes mal wenn irgendwo geklickt wird der VBA Code abgespielt wird. Dementsprechend kann man die Vorwärts und Rückwärts Buttons nicht mehr verwenden :(

Dies ist leider sehr unpraktisch. Ich habe in die Zellen etwas eingefügt und wollte dann es rückgängig machen und nicht mal das geht.
Daher die Frage, gibt es hier ein Workaround? Oder eventuell eine ganz andere Lösung ohne VBA?

Danke vorab

...zum Beitrag

VBA CODE Zellen nach bestimmten Textinhalt suchen und Zeile Färben?

Hallo, kann mir bitte jemanden mit dem Code behilflich sein?

Dieser Code sucht die Zellen in Spalte"A" nach bestimmten Textinhalt ab und bei Treffer löscht er die zughörige gesamte Zeile. Meine Frage: kann man diesen Code so ändern, dass anstatt die gefundene Zeile zu löschen nur mit einer Farbe z.B. Orange (44) färbt? Vielen Dank im Voraus.

Sub zeilenloeschen()
  Dim i As Long
  Dim letzteZeile As Long
  Dim suchBereich As Range
  Dim gefunden As Range
  Dim ersterTreffer As String
  Dim suchWert As String
  suchWert = "1" 'hier kommt dein Suchwert rein
  
  With ThisWorkbook.Sheets("Sheet4")
    letzteZeile = .Range("A" & Rows.Count).End(xlUp).Row
    Set suchBereich = .Range("A1:A" & letzteZeile)
    Set gefunden = suchBereich.Find(What:=suchWert, LookAt:=xlPart)
    If Not gefunden Is Nothing Then
      ersterTreffer = gefunden.Address
      Do
        gefunden.Activate
        ActiveCell.EntireRow.Delete shift:=xlUp
        Set gefunden = suchBereich.Find(What:=suchWert, LookAt:=xlPart)
      Loop While Not gefunden Is Nothing
    End If
  End With
End Sub

...zum Beitrag

VBA UserForm KeyDown Sonderzeichen werden nicht erkannt?

Hallo, ich habe eine Frage zu meinem VBA Code. Und zwar sollen verschiedene Tastatureingaben verschiedene Funktionen triggern. Bei Zahlen, Buchstaben, Plus, Minus, "&"-Zeichen, "!"-Zeichen und einige andere Tasten gehen. "*"-Zeichen, Prozent, "(" und ")" bspw. gehen jedoch NICHT (als KeyCode wird 0 zurückgegeben). Hat jemand eine Ahnung woran das liegt?

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  handleKeyDown KeyCode, Shift
End Sub

Private Sub handleKeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  Dim keyChar As String * 1
  keyChar = Chr(KeyCode)
  MsgBox (KeyCode)
  Select Case keyChar
  Case "0"
    numb0_Click
  Case "1"
    numb1_Click
  '.....
  Case Else
  End Select
  Select Case KeyCode
  Case "187"
    plus_Click
  '....
  Case Else 
  End Select
End Sub

Danke für alle Antworten,

Nicolas

...zum Beitrag

Wie suche ich mittels VBA nach einem Wert in einer anderen Datei?

Hallo, folgendes Problem: ich habe zwei Excel-Dateien, in beiden sind Datensätze mit eindeutigen Nummern. Jetzt soll es in Datei-A nacheinander jede Zeile der Spalte A nehmen, in Datei-B danach suchen und wenn es nicht gefunden wurde, soll es die nicht gefundene Zeile aus Datei-A in Datei-B an die letzte Stelle anfügen. Und das soll es mit jeder einzelnen Zeile aus Datei-A machen (also mit einer While-Schleife).

Kann mir wer helfen?

...zum Beitrag

Excel VBA - Umkehrfunktion, und alle Zeilen nach Begriff durchsuchen?

Hallo,

ich habe ein Problem mit Excel VBA. Ich habe eine Arbeitstabelle, die inzwischen über 76000 Zeilen beinhaltet. Deshalb möchte ich mit VBA eine Userform erstellen, die das Suchen einer bestimmten Zeile leichter macht.

Bis jetzt läuft es ganz gut, aber ich habe 1. das Problem, dass wenn ich einen Suchbegriff in eines der Textfelder eingebe, er mir immer nur die erste Zeile markiert, in der der Begriff steht. Da aber der Begriff noch öfter vorkommt, bringt es mir ja nichts, nur die erste Zeile markiert zu bekommen.

Das 2. Problem ist, dass bis jetzt nur die gesuchte Zeile farbig markiert wird. Ich möchte es aber hinbekommen, dass alle Zeilen, bei denen der Suchbegriff nicht vorkommt, ausgeblendet werden.

Hier mein bisheriges Skript für das erste Suchkriterium:

Sub Suche_Click()

Dim x As String

Dim Z As Integer

Dim temp As Integer

Z = Sheets(1).UsedRange.Rows.Count

x = Belegnr1 [Textbox-Name]

temp = 0

For i = 2 To Z

If Cells(i, 1) = x Then

temp = 1

Exit For

End If

If temp = 1 Then

ActiveSheet.Rows(i).Interior.ColorIndex = 6

' ActiveSheet.Rows(i).Hidden = False [Hier Problem Nr.2 ! ]

' ActiveSheet.Rows.Hidden = True

Else

' ActiveSheet.Rows.Hidden = True

MsgBox "Belegnummer nicht vorhanden. Bitte prüfen Sie ihre Eingabe."

End If

End Sub

Danke im Voraus!

...zum Beitrag

VBA Laufzeitfehler/ Error/ Programm hängt sich auf?

Hallo zusammen,

Ich habe ein Makro geschrieben, mit welchem jeweils via Button eine neue Spalte eingefügt wird.

Über den Button Spalten entfernen wird die jeweils ausgewählte Spalte gelöscht.

Private Sub CmdSpaltePlus_Click() 'Test Spalte hinzufügen

Dim spalte, zeile As Integer

Application.ScreenUpdating = False

spalte = 4

Do Until Cells(2, spalte).Value = "Perfektion"

   spalte = spalte + 1

Loop

Columns("D:E").Copy

Columns(spalte).Insert Shift:=xlToRight

Range(Cells(2, spalte - 2), Cells(2, spalte + 1)).Merge

Range(Cells(4, spalte), Cells(4, spalte + 1)).Merge

Cells(3, spalte).Value = ""

Cells(3, spalte + 1).Value = ""

Cells(4, spalte).Value = ""

Cells(4, spalte + 1).Value = ""

zeile = 6

 

Do Until zeile = 1000

   If Left(Cells(zeile, 1).Value, 1) = "S" Then

        Cells(zeile, spalte).Value = ""

       Cells(zeile, spalte + 1).Value = ""

    End If

   zeile = zeile + 1

Loop

Application.ScreenUpdating = True

End Sub

 

Private Sub CmdPerfektionPlus_Click() 'Perfektion Spalte hinzufügen

Dim spalte, zeile As Integer

Application.ScreenUpdating = False

spalte = 4

Do Until Cells(2, spalte).Value = "F.n.B."

   spalte = spalte + 1

Loop

Columns("D:E").Copy

Columns(spalte).Insert Shift:=xlToRight

Range(Cells(2, spalte - 2), Cells(2, spalte + 1)).Merge

Range(Cells(4, spalte), Cells(4, spalte + 1)).Merge

Cells(3, spalte).Value = ""

Cells(3, spalte + 1).Value = ""

Cells(4, spalte).Value = ""

Cells(4, spalte + 1).Value = ""

zeile = 6

Do Until zeile = 1000

   If Left(Cells(zeile, 1).Value, 1) = "S" Then

        Cells(zeile, spalte).Value = ""

       Cells(zeile, spalte + 1).Value = ""

    End If

   zeile = zeile + 1

Loop

Application.ScreenUpdating = True

End Sub

Spalten löschen

Private Sub Kompetenzenentfernen_Click() 'Spalten löschen

ActiveSheet.Unprotect Password:="MNPS"

If Cells(5, ActiveCell.Column) = "Schicht" Or Cells(5, ActiveCell.Column) = "Nr." Or Cells(5, ActiveCell.Column) = "Name" Or Cells(3, ActiveCell.Column) = "A1" Or Cells(3, ActiveCell.Column) = "D1" Or Cells(9, ActiveCell.Column) = "." Or Cells(3, ActiveCell.Column) = "E1" Or Cells(3, ActiveCell.Column) = "F1" Or Cells(3, ActiveCell.Column) = "G1" Or Cells(3, ActiveCell.Column) = "H1" Or Cells(2, ActiveCell.Column) = "." Or Cells(1, ActiveCell.Column) = "Zielwert" Then

MsgBox "Spalte kann nicht gelöscht werden"

Else

Selection.EntireColumn.Delete Shift:=xlUp

End If

'ActiveSheet.Protect Password:="MNPS"

End Sub

Die Buttons funktionieren auch alle, nur leider kommt oft eine Laufzeitfehlerneldung oder das ganze Programm hängt sich auf wenn ich erst mit dem einen Button eine Spalte eingefügt habe und im Anschluss mit dem anderen Button eine Spalte einfügen.

Es funktioniert also leider nicht verlässlich. Hat jemand eine Idee woran das liegen könnte?

Vielen Dank im Voraus!

...zum Beitrag

Excel VBA Code, um Zeilen, die eine bestimmte Bedingung nicht erfüllen, zu löschen?

Hi, ich hab folgendes Problem:

Ich soll aus einer komplexen Excel Tabelle jene Zeilen löschen, die nicht zu den anderen passen bzw. eine bestimmte Bedingung nicht erfüllen. Um die riesige Liste nicht händisch durchsuchen zu müssen, suche ich nach einer Möglichkeit, die Datenbereinigung in Excel VBA umzusetzen.

Hier ein Beispiel, welches mein Problem etwas genauer beschreibt.

Zu jedem Beispiel in Spalte A gehören in Spalte B die sortierten Werte 1, 2 und 3. Sind einem Beispiel nur 1 oder 2 Werte zugewiesen (siehe Beispiel 3) oder enthält ein Beispiel falsche Werte (siehe Beispiel 5 - falscher Wert "4"), dann sollen diese Zeilen komplett gelöscht werden.

Am Ende sollen also nur mehr die Zeilen 1,2,3,4,5,6,9,10,11 übrig bleiben.

Kann mir da jemand helfen, wie ich diese Bedingung mit den 3 zugehörigen Werten in VBA umgesetzt bekomme?

...zum Beitrag

Was möchtest Du wissen?

Deinen Beitrag erstellen