Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1068to1072
1068to1072
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Erste Zeile und Spalte mit Daten ermitteln

Forumthread: Erste Zeile und Spalte mit Daten ermitteln

Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 10:39:44
Daniel
Hallo Community,
ich habe mir ein Makro gebastelt, dass mir im aktiven Tabellenblatt auf Knopfdruck einen mit Daten gefüllten Bereich formatiert mit einer Überschriftszeile und danach im Wechsel jede zweite Zeile zur besseren Übersicht farbig. Das klappt soweit ganz hervorragend, allerdings nur, wenn der Datenbereich in Zelle A1 beginnt. Wie kann ich nun Variablen in das Makro einbauen, so dass die erste gefüllte Zeile und Spalte berücksichtigt werden und die Formatierung erst dort beginnt?
Bisher sieht das Ganze so aus:

Sub ZeilenEinfärben()
Dim Zeile As Integer
Dim ZeileMax As Integer
Dim Spalte As Integer
Dim SpalteMax As Integer
With ActiveSheet
ZeileMax = .UsedRange.Rows.Count
SpalteMax = .UsedRange.Columns.Count
For Spalte = 1 To SpalteMax
.Cells(1, Spalte).Interior.Color = RGB(17, 51, 136)
.Cells(1, Spalte).Font.Color = RGB(255, 255, 255)
Next Spalte
For Zeile = 2 To ZeileMax Step 2
For Spalte = 1 To SpalteMax
.Cells(Zeile, Spalte).Interior.Color = RGB(255, 255, 255)
Next Spalte
Next Zeile
For Zeile = 3 To ZeileMax Step 2
For Spalte = 1 To SpalteMax
.Cells(Zeile, Spalte).Interior.Color = RGB(216, 216, 213)
Next Spalte
Next Zeile
End With
End Sub


Viele Grüße
Daniel

Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 11:11:23
Tino
Hallo,
teste mal diesen Code.
Es werden z.Z. nur Zellen mit Werten berücksichtigt,
sollten auch Formeln enthalten sein müsste der Code eventuell erweitert werden.
Fehlerbehandlung habe ich auch nicht eingebaut.
Sub ZeilenEinfärben()
Dim A As Long, Zeile As Long, SpalteMax As Long, ZeileMax As Long
Dim rErste As Range


With Application
 .ScreenUpdating = False

    Set rErste = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, 1, 1, 1, False, False, False)
    SpalteMax = Cells(rErste.Row, Columns.Count).End(xlToLeft).Column
    
    With Range(rErste, Cells(rErste.Row, SpalteMax))
        
        .Interior.Color = RGB(17, 51, 136)
        .Font.Color = RGB(255, 255, 255)
    
        ZeileMax = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, 1, 1, 2, False, False, False).Row
        
        A = 1
        For Zeile = rErste.Row + 1 To ZeileMax Step 2
           .Offset(A, 0).Interior.Color = RGB(255, 255, 255)
           A = A + 2
        Next Zeile
        
        A = 2
        For Zeile = rErste.Row + 2 To ZeileMax Step 2
          .Offset(A, 0).Interior.Color = RGB(216, 216, 213)
          A = A + 2
        Next Zeile
    
    End With
 
 .ScreenUpdating = True
End With
End Sub


Gruß Tino

Anzeige
noch eine ohne Schleife
14.04.2009 11:50:46
Tino
Hallo,
hier noch eine Version mit der Bedingten Formatierung dafür ohne Schleife.
Sub ZeilenEinfärben()
Dim A As Long, Zeile As Long, SpalteMax As Long, ZeileMax As Long
Dim rErste As Range

    Set rErste = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, 2, 1, 1, False, False, False)
    SpalteMax = Cells(rErste.Row, Columns.Count).End(xlToLeft).Column
    
     With Range(rErste, Cells(rErste.Row, SpalteMax))
        .Interior.Color = RGB(17, 51, 136)
        .Font.Color = RGB(255, 255, 255)
     End With
     
     On Error Resume Next
      ZeileMax = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, 2, 1, 2).Row
      ZeileMax = Application.Max(ZeileMax, Cells.Find("*", Cells(Rows.Count, Columns.Count), xlFormulas, 2, 1, 2).Row)
     On Error GoTo 0
        
     Set rErste = Range(rErste.Offset(1, 0), Cells(ZeileMax, SpalteMax))
     
     With rErste
        .FormatConditions.Delete
        
        .FormatConditions.Add xlExpression, , "=REST(ZEILE();2)=1"
        .FormatConditions(1).Interior.Color = RGB(255, 255, 255)
        
        .FormatConditions.Add xlExpression, , "=REST(ZEILE();2)=0"
        .FormatConditions(2).Interior.Color = RGB(216, 216, 213)

     End With

End Sub


Gruß Tino

Anzeige
AW: Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 11:15:29
Daniel
Hi
probiers mal so:

With ActiveSheet.Usedrange
ZeileMax = .Rows.Count
SpalteMax = .Columns.Count


der Rest kann so bleiben, wie er ist.
Gruß, Daniel

AW: Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 11:15:42
Matthias
Hallo
So kannst Du den Bereich ermitteln

Option Explicit
Sub BeginnFinden()
Dim rng As Range
Dim ZeileMin As Long
Dim SpalteMin As Long
Dim ZeileMax As Long
Dim SpalteMax As Long
With ActiveSheet
For Each rng In .UsedRange
If Not IsEmpty(rng) Then Exit For
Next
ZeileMin = rng.Row
SpalteMin = rng.Column
ZeileMax = .UsedRange.Rows.Count
SpalteMax = .UsedRange.Columns.Count
End With
MsgBox "ZeileMIN = " & ZeileMin   'Test
MsgBox "SpalteMIN = " & SpalteMin 'Test
MsgBox "ZeileMax = " & ZeileMax   'Test
MsgBox "SpalteMax = " & SpalteMax 'Test
Range(Cells(ZeileMin, SpalteMin), Cells(ZeileMax, SpalteMax)).Select
End Sub


Den Rest hast Du ja schon.
Gruß Matthias

Anzeige
AW: Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 11:51:34
Daniel
Hallo Matthias,
ich habe deinen Code noch leicht modifiziert, da ZeileMax und SpalteMax nur von Zelle A1 aus gezählt wurden. Wenn bspw. Spalte A leer war, wurde also im Bereich eine Spalte zu wenig markiert.
Angepasst habe ich lediglich hier:
ZeileMax = .UsedRange.Rows.Count + ZeileMin - 1
SpalteMax = .UsedRange.Columns.Count + SpalteMin - 1
Funktioniert nun bestens. :)
Daniel
Anzeige
AW: Erste Zeile und Spalte mit Daten ermitteln
14.04.2009 11:40:49
Daniel
Super, die Lösung von Daniel war genau die, die ich brauchte. Und so einfach... *an die Stirn hau* Die beiden anderen Lösungen werde ich mir gleich noch mal genauer anschauen. Die Problematik, dass ich Anfangszeile oder -spalte benötige und nicht mit Usedrange arbeiten kann, wird definitiv mal auf mich zukommen. Von daher wieder was dazugelernt und euch allen einen herzlichen Dank! :-)
Daniel
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Erste Zeile und Spalte mit Daten ermitteln in Excel

Schritt-für-Schritt-Anleitung

Um die erste Zeile und Spalte mit Daten in Excel zu ermitteln und diese formatieren zu können, kannst Du folgenden VBA-Code verwenden. Dieser Code berücksichtigt die ersten gefüllten Zellen und formatiert den Datenbereich entsprechend.

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Füge ein neues Modul hinzu, indem Du mit der rechten Maustaste auf "VBAProject (DeineArbeitsmappe)" klickst und "Einfügen" > "Modul" wählst.
  3. Kopiere den folgenden Code in das Modul:
Sub BeginnFinden()
    Dim rng As Range
    Dim ZeileMin As Long
    Dim SpalteMin As Long
    Dim ZeileMax As Long
    Dim SpalteMax As Long
    With ActiveSheet
        For Each rng In .UsedRange
            If Not IsEmpty(rng) Then Exit For
        Next
        ZeileMin = rng.Row
        SpalteMin = rng.Column
        ZeileMax = .UsedRange.Rows.Count + ZeileMin - 1
        SpalteMax = .UsedRange.Columns.Count + SpalteMin - 1
    End With
    MsgBox "ZeileMIN = " & ZeileMin  'Test
    MsgBox "SpalteMIN = " & SpalteMin 'Test
    MsgBox "ZeileMax = " & ZeileMax   'Test
    MsgBox "SpalteMax = " & SpalteMax 'Test
    Range(Cells(ZeileMin, SpalteMin), Cells(ZeileMax, SpalteMax)).Select
End Sub
  1. Schließe den VBA-Editor und führe das Makro aus, um die erste Zeile und Spalte mit Daten zu ermitteln.

Häufige Fehler und Lösungen

  • Fehler: Das Makro funktioniert nicht, weil die Daten in einer anderen Zeile oder Spalte beginnen.

    • Lösung: Stelle sicher, dass Du den Code anpasst, um die ersten gefüllten Zellen korrekt zu erfassen.

  • Fehler: Es werden leere Zeilen oder Spalten in den Ergebnissen angezeigt.

    • Lösung: Überprüfe, ob alle Werte in der UsedRange korrekt erfasst werden und dass keine leeren Zellen in der ersten Zeile oder Spalte vorhanden sind.

Alternative Methoden

Eine alternative Methode ist die Verwendung von bedingter Formatierung, um die Zeilen farblich abzuheben. Hierzu kannst Du den folgenden Code verwenden:

Sub ZeilenEinfärben()
    Dim rErste As Range
    Dim ZeileMax As Long
    Dim SpalteMax As Long

    Set rErste = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, 1, 1, 1, False, False, False)
    SpalteMax = Cells(rErste.Row, Columns.Count).End(xlToLeft).Column

    With Range(rErste, Cells(rErste.Row, SpalteMax))
        .Interior.Color = RGB(17, 51, 136)
        .Font.Color = RGB(255, 255, 255)
    End With
End Sub

Diese Methode erfordert keine Schleifen und kann schneller ausgeführt werden.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie Du die ermittelten Zeilen und Spalten in der Praxis verwenden kannst:

  • Datenanalyse: Verwende den ermittelten Bereich, um Diagramme zu erstellen oder Pivot-Tabellen zu generieren.
  • Berichterstellung: Formatierte Datenabschnitte können in Berichten hervorgehoben werden, um die Lesbarkeit zu verbessern.

Tipps für Profis

  • Nutze Option Explicit am Anfang Deines VBA-Codes, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
  • Teste Deinen Code mit verschiedenen Datensätzen, um sicherzustellen, dass er robust ist und auch in unerwarteten Situationen funktioniert.
  • Verwende ScreenUpdating = False, um die Leistung zu verbessern, wenn Du mit großen Datenmengen arbeitest.

FAQ: Häufige Fragen

1. Frage
Wie kann ich den Code anpassen, wenn ich nur Daten mit Formeln berücksichtigen möchte?
Antwort: Ersetze xlValues durch xlFormulas in den Find-Methoden, um auch Zellen mit Formeln zu berücksichtigen.

2. Frage
Kann ich das Makro für mehrere Arbeitsblätter verwenden?
Antwort: Ja, Du kannst eine Schleife durch alle Arbeitsblätter in der Arbeitsmappe implementieren, um dieses Makro für jedes Blatt anzuwenden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige