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

Letzte Zeile ohne Formel finden

Forumthread: Letzte Zeile ohne Formel finden

Letzte Zeile ohne Formel finden
06.02.2019 10:36:13
Christian
Hallo schon wieder,
ich möchte in einem Tabellenblatt in der Spalte B ab B7 bis B100 den Inhalt der Zellen überprüfen. In den Zellen stehen Formeln die im Normalfall leer "" sind, aber natürlich gibt es da auch Werte aus anderen Tabellenblättern die dorthin übertragen werden.
Nun soll im Tabellenblatt die letzte Zelle mit (numerischen) Inhalt gefunden werden, der Bereich wieder zurück nach "oben" bis Spalte N7 markiert werden und die Formeln "gelöscht" werden und der Inhalt der Zellen übernommen werden (.value = .value). Quasi das, was ich auch machen würde, wenn ich kopiere und dann nur die Werte einfüge.
Ich hab das gefunden ->
https://www.herber.de/forum/archiv/1036to1040/1039758_VBA_letzte_Zeile_Ermitteln.html
hilft mir aber nicht weiter.
So habe ich es probiert, 
Sub Bereich_Formelweg()
Dim wS As Worksheet
maxZeile = WorksheetFunction.Match("", Sheets("Februar").Columns("b"), -1) - 1
For Each wS In ActiveWorkbook.Windows(1).SelectedSheets
With wS.Range("b7" & maxZeile & ":N" & maxZeile & "")
.Interior.ColorIndex = 3  'zum testen was passiert!!!
'.Value = .Value
End With
Next wS
End Sub

Vielleicht kann mir ja jemand helfen.
Danke
Christian
Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Letzte Zeile ohne Formel finden
06.02.2019 10:40:57
Rainer
Evtl. so?
maxZeile = 7
For i = 7 to 100
if Cells(i,2)  "" Then maxZeile = i
Next i
Gruß,
Rainer
AW: Letzte Zeile ohne Formel finden
06.02.2019 11:09:33
Christian
Hallo Rainer,
wie sähe denn der Ganze Code aus? V.a. das bei "With wS.range...."
Gruß
Christian
AW: Letzte Zeile ohne Formel finden
06.02.2019 11:24:13
Rainer
Wahrscheinlich so, ich habe es aber mangels Beispieldatei nicht getestet.
Sub Bereich_Formelweg()
Dim wS As Worksheet
For Each wS In ActiveWorkbook.Windows(1).SelectedSheets
maxZeile = 7
For i = 7 to 100
if wS.Cells(i,2)  "" Then maxZeile = i
Next i
N = 14
With wS.Range(wS.Cells(7,2),wS.Cells(N,maxZeile))
.Interior.ColorIndex = 3  'zum testen was passiert!!!
'.Value = .Value
End With
Next wS
End Sub

Anzeige
AW: Letzte Zeile ohne Formel finden
06.02.2019 12:20:02
Christian
Hallo Rainer,
nein, leider funktioniert das nicht. Der Arbeitsbereich bei dieser Funktion erstreckt sich von B7 bis I14. Und ob in der Zelle eine Formel oder etwas anderes steht funktioniert nicht. Sobald ich das .value wieder aktiviere sind alle "leeren" Formeln auch furt, und es ist kein Inhalt mehr vorhanden.
Gruß
Anzeige
AW: Letzte Zeile ohne Formel finden
06.02.2019 13:02:02
Rainer
Hallo Christian,
dann ist deine Beschreibung falsch. Du hast offenbar zwischen den Werten auch leere Zellen. Dann kannst du es nicht mit "Range" machen.
Ich vermute mal das Werners Lösung hilft. Wenn nicht, dann bitte mit Beispieldatei!
Gruß,
Rainer
AW: Letzte Zeile ohne Formel finden
06.02.2019 15:47:24
Christian
Hallo Rainer,
ja, das ist richtig. Da wären tatsächlich auch leere Zeilen.
Danke trotzdem
Christian
Anzeige
per Formel: letzte numerische Zelle ohne Formel
06.02.2019 10:52:34
WF
Hi,
folgende Arrayformel:
{=MAX(ISTZAHL(B7:B100)*NICHT(ISTFORMEL(B7:B100))*ZEILE(X7:X100))}
WF
AW: per Formel: letzte numerische Zelle ohne Formel
06.02.2019 12:09:51
Christian
Hallo WF,
wohin muss denn diese MatrixFormel?
Gruß
? wo Du willst ?
06.02.2019 12:20:26
WF
.
Anzeige
AW: ? wo Du willst ?
06.02.2019 12:32:52
Christian
Hab ich probiert, Formel bleibt aber leider Formel
Was macht diese Funktion, und was hat das mit der ZEILE X7 auf sich?
=MAX(ISTZAHL(B7:B100)*NICHT(ISTFORMEL(B7:B100))*ZEILE(x7:x100))
AW: hierzu ...
06.02.2019 12:37:52
neopa
Hallo Christian,
... WF hat offensichtlich nur einen Teil Deiner Ausgangsfragestellung bei seiner Antwort in Erinnerung gehabt. Die letzte Zahl kann man mit Formel einfacher mit =VERWEIS(9^99;B7:B100) und dessen zugehörige Zeilennummer mit =VERWEIS(9^99;B7:B19;ZEILE(B7:B100)) ermitteln. Deine eigentliche Zielstellung kannst Du aber nur mit VBA erreichen. Aus VBA halten sich sowohl WF und ich jedoch außen vor.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
06.02.2019 12:45:57
Christian
Ah, ich dachte mir das schon. Nur VBA.
Danke trotzdem
Gruß
AW: hierzu ...
06.02.2019 12:56:52
Werner
Hallo Christian,
meinst du das: 
Sub Schaltfläche1_Klicken()
Dim raZelle As Range
'Blattname anpassen
For Each raZelle In Worksheets("Tabelle1").Range("A7:N100")
If raZelle.Value  "" Then
raZelle.Value = raZelle.Value
End If
Next raZelle
End Sub
Gruß Werner
Anzeige
AW: Du bist der Beste
06.02.2019 13:00:11
Christian
Ha,
ja genau das.
Danke Werner, du bist der Beste!!!
Gruß
Christian
Gerne u. Danke für die Rückmeldung. o.w.T.
06.02.2019 13:03:58
Werner
;
Anzeige
Anzeige

Infobox / Tutorial

Letzte Zeile ohne Formel finden in Excel

Schritt-für-Schritt-Anleitung

Um die letzte Zelle mit numerischem Inhalt in einer bestimmten Spalte zu finden und die Formeln in Werte umzuwandeln, kannst du den folgenden VBA-Code verwenden. Dieser Code durchsucht die Zellen von B7 bis B100 und überträgt die Werte in den Bereich von B7 bis N7:

Sub Bereich_Formelweg()
    Dim wS As Worksheet
    Dim maxZeile As Long
    Dim i As Long

    For Each wS In ActiveWorkbook.Windows(1).SelectedSheets
        maxZeile = 7
        For i = 7 To 100
            If wS.Cells(i, 2) <> "" Then maxZeile = i
        Next i

        With wS.Range(wS.Cells(7, 2), wS.Cells(14, maxZeile))
            .Interior.ColorIndex = 3  ' Farbe zur Überprüfung setzen
            .Value = .Value            ' Formeln durch Werte ersetzen
        End With
    Next wS
End Sub

Dieser Code wird alle Formeln in dem angegebenen Bereich in Werte umwandeln.

Häufige Fehler und Lösungen

  • Problem: Der Code funktioniert nicht, wenn es leere Zellen gibt.

    • Lösung: Stelle sicher, dass der Bereich, den du untersuchst, tatsächlich Daten enthält. Leerzellen können das Ergebnis beeinflussen.

  • Problem: Die Formeln bleiben bestehen.

    • Lösung: Achte darauf, dass die Zeile .Value = .Value nicht auskommentiert ist, da dies die Formeln durch die Werte ersetzt.

Alternative Methoden

Wenn du keine VBA-Lösung verwenden möchtest, kannst du auch folgende Excel-Formel probieren, um die letzte numerische Zelle zu finden:

=VERWEIS(9^99;B7:B100)

Diese Formel gibt dir den letzten numerischen Wert in dem angegebenen Bereich zurück.

Praktische Beispiele

Angenommen, du hast in deinem Excel-Dokument folgende Werte in der Spalte B:

  • B7: (Formel)
  • B8: (leer)
  • B9: 10
  • B10: 15
  • B11: (Formel)

Wenn du den oben genannten VBA-Code ausführst, wird der Bereich von B7 bis N11 markiert und die Formeln in B9 und B10 werden durch die Werte 10 und 15 ersetzt.

Tipps für Profis

  • Verwende Application.ScreenUpdating = False am Anfang deines VBA-Codes, um die Performance zu verbessern, besonders bei großen Datenmengen.
  • Setze am Ende Application.ScreenUpdating = True, um die Excel-Oberfläche wieder zu aktualisieren.
  • Wenn du oft mit VBA arbeitest, ist es hilfreich, sich mit dem Debugging in der VBA-Entwicklungsumgebung vertraut zu machen.

FAQ: Häufige Fragen

1. Wie kann ich den Code anpassen, um einen anderen Bereich zu durchsuchen? Du kannst die Zeilen B7:B100 und N7 im Code anpassen, um den gewünschten Bereich zu ändern.

2. Funktioniert dieser Code in allen Excel-Versionen? Ja, dieser VBA-Code sollte in allen modernen Excel-Versionen ab Excel 2007 funktionieren.

3. Was ist der Unterschied zwischen .Value und .Formula? .Value gibt den aktuellen Wert einer Zelle zurück, während .Formula den Inhalt der Zelle als Formel behandelt.

Beliebteste Forumthreads (12 Monate)

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