Schlagwort: Zellen

Veröffentlicht am

Leere Zeilen aus Excel Tabellen löschen

Es kommt ja öfters vor, dass in einer Excel Tabelle leere Zeilen enthalten sind, die man nicht gebrauchen kann. Durch leere Zeile lässt sich z.B. kein anständiger Filter setzen, um den Inhalt zu sortieren.

Bevor man nun umständlich mit VBA versucht die leeren Zeilen zu löschen, kann man das auch ganz einfach mit den Excel Bordwerkzeugen machen.

Ich habe in eine Tabelle vorbereitet, in der viele leere Zeilen vorhanden sind, die ich löschen möchte. Um das ein bisschen übersichtlicher zu gestalten, die blauen Zeilen sollen unbedingt erhalten bleiben und die weißen Zeilen dazwischen sollen gelöscht werden.

Leere Zeilen aus Excel Tabelle löschen

Markiert nun die Spalte in der sich die leeren Zellen der Zeile befinden, die ihr entfernen möchtet und drückt dann die Taste F5.
Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Leere Zeilen aus Excel Tabelle löschen

Veröffentlicht am

Formatierung von Zellen

Die Formatierung von Zellen ist mit Hilfe von VBA-Makros ebenso leicht durchzuführen, wie mit Excel selbst.
Am nachfolgenden Beispiel wird erklärt, wie definierte Zellen mit einem Befehl formatiert werden.

Anforderung

  • Formatierung, die durch den Standard-Excel Benutzer nicht geändert werden kann, also sehr schnell wieder hergestellt werden kann.
  • Immer drei nebeneinanderliegende Zellen sollen mit einer unterschiedlichen Hintergrundfarbe formatiert werden.

Beispiel

Das Beispiel baut teilweise auf auf.

Im Screenshot ist die Zeile, die mit dem Makro benannt werden soll farblich hervorgehoben.

In diesem Beispiel wird die neue Benennung der Zellen aufgegriffen, deren Vorteile die bereits im Artikel ausführlich erläutert wurde.

Code: Orange Formatierung

Private Sub KapFarben_orange()                              
' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett

    Range("Kap_7_3:Kap_7_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_5_3:Kap_5_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_3_3:Kap_3_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_1_3:Kap_1_1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With


Die einzelnen Punkt der Formatierung sind ab Besten mit dem Makro-Rekorder aufzuzeichnen und dann anzupassen. Eine manuelle Eingabe ist hier wenig sinnvoll.

Der Code ist in 4 Teile getrennt.

  • Range(„Kap_7_3:Kap_7_1“)
  • Range(„Kap_5_3:Kap_5_1“)
  • Range(„Kap_3_3:Kap_3_1“)
  • Range(„Kap_1_3:Kap_1_1“)

Das bedeutet nicht anderes als die Zusammenfassung der Bereichen Kap_7_3, Kap_7_2, Kap_7_1 zu („Kap_7_3:Kap_7_1“) usw. Grundlage bildet die Benennung der Zellen von C7 zu Kap_7_3.
Die nachfolgenden Abschnitte sind identisch, da die gleiche Formatierung für alle 4 Bereiche gleich sein soll, deshalb sind sie einfach zu kopieren. Die Range ist aber abzuändern.

Nach dem fertigstellen der Makros kann es mit F5 ausgeführt werden. Die Zellen sollten sich dann automatisch formatieren.

Für die blau eingefärbten Zellen ist analog zu verfahren.
➡ Makro-Rekorder
➡ Code anpassen

Verknüpfung

Im Artikel wurde beschrieben, wie die Zellen mit Hilfe eines VBA.Makros beschrieben werden können. Es bietet sich nun an, beide Makros zu verknüpfen. Beide lassen sich sehr leicht verknüpfen. Die Reihenfolge im nachfolgenden Code ist

  1. Beschriftung
  2. Formatierung

      3. Code

      
Option Explicit


Sub KapBennennung()

Dim strRegal7 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal7 = "Regal 7"

Dim strRegal6 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal6 = "Regal 6"

Dim strRegal5 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal5 = "Regal 5"

Dim strRegal4 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal4 = "Regal 4"

Dim strRegal3 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal3 = "Regal 3"

Dim strRegal2 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal2 = "Regal 2"

Dim strRegal1 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal1 = "Regal 1"

Dim strNum1 As String               ' Zur Beschriftung der Zellen mit -1
strNum1 = "-1"

Dim strNum2 As String               ' Zur Beschriftung der Zellen mit -2
strNum2 = "-2"

Dim strNum3 As String               ' Zur Beschriftung der Zellen mit -3
strNum3 = "-3"


Sheets("Kapazitaet").Select

    Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
    Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
    Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1
    
    Range("Kap_6_3").Value = strRegal6 & strNum3
    Range("Kap_6_2").Value = strRegal6 & strNum2
    Range("Kap_6_1").Value = strRegal6 & strNum1

    Range("Kap_5_3").Value = strRegal5 & strNum3
    Range("Kap_5_2").Value = strRegal5 & strNum2
    Range("Kap_5_1").Value = strRegal5 & strNum1
    
    Range("Kap_4_3").Value = strRegal4 & strNum3
    Range("Kap_4_2").Value = strRegal4 & strNum2
    Range("Kap_4_1").Value = strRegal4 & strNum1
        
    Range("Kap_3_3").Value = strRegal3 & strNum3
    Range("Kap_3_2").Value = strRegal3 & strNum2
    Range("Kap_3_1").Value = strRegal3 & strNum1
    
    Range("Kap_2_3").Value = strRegal2 & strNum3
    Range("Kap_2_2").Value = strRegal2 & strNum2
    Range("Kap_2_1").Value = strRegal2 & strNum1
    
    Range("Kap_1_3").Value = strRegal1 & strNum3
    Range("Kap_1_2").Value = strRegal1 & strNum2
    Range("Kap_1_1").Value = strRegal1 & strNum1

Call KapFarben_orange
Call KapFarben_blau

    Range("A1").Select
    
End Sub

Private Sub KapFarben_orange()                              ' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett

    Range("Kap_7_3:Kap_7_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_5_3:Kap_5_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_3_3:Kap_3_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_1_3:Kap_1_1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
End Sub

    
Private Sub KapFarben_blau()                                                ' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett

    Range("Kap_6_3:Kap_6_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_4_3:Kap_4_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_2_3:Kap_2_1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
        Range("Kap_Summe").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
End Sub

      Mittels Call-Befehl werden die Formatierungsmakros KapFarben_orange und KapFarben_blau zusammen aufgerufen, danach wird der Cursor auf die Zelle A1 gesetzt.

Veröffentlicht am

Ermittlung der letzten befüllten Zeile in einem Tabellenblatt

Mit einem Makro lässt sich in Excel sehr einfach und schnell die letzte beschriebene Zeile in einem Tabellenblatt ermitteln.
Über eine MsgBox wird die Zeilennummer dem User ausgegeben.

Sub Letzte_beschriebene_Zeile()
'
' Das Makro ermittelt die letzte beschriebene Zeile
'
    Dim letzte As Long
    loletzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    MsgBox letzte 
End Sub

Veröffentlicht am

Flexible Beschriftung von Zellen

In Excel sollen mehrere Zellen mit einem unterschiedlichen Wert befüllt werden.

Anforderung

  • Die Beschriftung muss flexibel gestaltet werden, so dass Sie schnell geändert werden kann
  • Der Inhalt muss kopiert werden können

Die Verwendung einer Formel, wie z.B. ist unmöglich, da beim Kopieren der Wert nicht übernommen werden kann. Die kopierte erzeugt einen Fehler. Es bleibt also nur das VBA-Makro, um die Zellen zu beschriften.

Beispiel

Im Screenshot ist die Zeile, die mit dem Makro benannt werden soll farblich hervorgehoben.

In jeder Zelle, C7, D7, E7, etc., ist ein Überblick zu bekommen, wie viele Stellplätze in Summe belegt sind. Die Beschriftung wird mit Hilfe eines VBA-Makros vorgenommen.

Damit man Punkt 1 der Anforderung erfüllen kann, also eine flexible Gestaltung der Tabelle gewährleistet, wurden die Zellen umbenannt. Dazu wurde in das Namensfeld ein neuer Wert eingetragen. Für die Zelle C7 wurde der Name Kap_7_3, für Zelle D7 der Name Kap_7_2, usw. eingetragen.

Die Umbenennung ist unbedingt vorzunehmen. Wird im Makro die Zelle nur mit C7 angesprochen und eine neue Zeile eingefügt, dann verschiebt sich der neue Wert im Makro nicht, wie in den Excel-Formeln, d.h.
C7, D7, E7, F7, G7
wird beim einfügen einer neuen Zeile NICHT zu
C8, D8, E8, F8, G8.
Das Makro spricht weiterhin die Zellen C7, D7, E7, F7, G7. Durch die Vergabe neuer Namen wird das Problem umgangen. Wird eine neue Zeile eingefügt, spricht das Makro weiterhin die richtigen Zellen an.

Code

Option Explicit

Sub KapBennennung()

Dim strRegal7 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal7 = "Regal 7"

Dim strRegal6 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal6 = "Regal 6"

Dim strRegal5 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal5 = "Regal 5"

Dim strRegal4 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal4 = "Regal 4"

Dim strRegal3 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal3 = "Regal 3"

Dim strRegal2 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal2 = "Regal 2"

Dim strRegal1 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal1 = "Regal 1"

Dim strNum1 As String               ' Zur Beschriftung der Zellen mit -1
strNum1 = "-1"

Dim strNum2 As String               ' Zur Beschriftung der Zellen mit -2
strNum2 = "-2"

Dim strNum3 As String               ' Zur Beschriftung der Zellen mit -3
strNum3 = "-3"


Sheets("Kapazitaet").Select

    Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
    Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
    Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1
    
    Range("Kap_6_3").Value = strRegal6 & strNum3
    Range("Kap_6_2").Value = strRegal6 & strNum2
    Range("Kap_6_1").Value = strRegal6 & strNum1

    Range("Kap_5_3").Value = strRegal5 & strNum3
    Range("Kap_5_2").Value = strRegal5 & strNum2
    Range("Kap_5_1").Value = strRegal5 & strNum1
    
    Range("Kap_4_3").Value = strRegal4 & strNum3
    Range("Kap_4_2").Value = strRegal4 & strNum2
    Range("Kap_4_1").Value = strRegal4 & strNum1
        
    Range("Kap_3_3").Value = strRegal3 & strNum3
    Range("Kap_3_2").Value = strRegal3 & strNum2
    Range("Kap_3_1").Value = strRegal3 & strNum1
    
    Range("Kap_2_3").Value = strRegal2 & strNum3
    Range("Kap_2_2").Value = strRegal2 & strNum2
    Range("Kap_2_1").Value = strRegal2 & strNum1
    
    Range("Kap_1_3").Value = strRegal1 & strNum3
    Range("Kap_1_2").Value = strRegal1 & strNum2
    Range("Kap_1_1").Value = strRegal1 & strNum1

    Range("A1").Select
    
End Sub

Erklärung zum Code

Im ersten Abschnitt wird eine Variable für jedes Regal angelegt, also

  • strRegal7 erhält den Namen Regal 7
  • strRegal6 erhält den Namen Regal 6
  • …
  • strRegal1 erhält den Namen Regal 1

Im zweiten Abschnitt werden die Nummern 1, 2, und 3 einer Variablen zugeordnet

  • strRegal1 erhält den Namen -1
  • strRegal2 erhält den Namen -2
  • strRegal3 erhält den Namen -3

Nachdem die Variablen angelegt und einen festen Namen erhalten haben, kann man nun beginnen, die Zellen zu Beschriften.

Zuerst wird das Tabellenblatt aufgerufen, dass die Werte enthalten soll. Das ist besonders bei mehreren Tabellen in einer Arbeitsmappe erforderlich, um zu vermeiden, dass nicht aus versehen eine falsche Beschriftung vorgenommen wird und keine anderen Werte überschrieben werden.
Der Aufruf der Tabelle erfolgt mit dem Befehl

Sheets("Kapazitaet").Select

Nun kann man beginnen die Beschriftung zu definieren:

    Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
    Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
    Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1

Die Funktionsweise ist ziemlich einfach.
Zu Beginn definiert man die Range, also die Zelle, die man beschriften will. Dazu ruft man nicht die Standard-Excelbezeichnung C7 auf, sondern den neuen Namen Kap_7_3. Nach dem Gleichheitszeichen (=) fügt man dann beide definierten Variablen strRegal7 und strNum3 zusammen. Im Tabellenblatt ergibt das dann den Wert Regal 7-3.
Mit den restlichen Tabellen verfährt man genau gleich, wie oben beschrieben.

Wendet man dieses Vorgehen zur Beschriftung an, dann sind lediglich ein paar Werte im VBA-Code zu ändern um den Zellen eine andere Beschriftung zu verpassen.

Der Befehl, bevor das Makro mit dem Befehl End Sub beendet wird setzt der Cursor in die Zelle A1.
In der VBA-Sprache heißt der Befehlt

Range("A1").Select