Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Letzte belegte Zelle mit VBA | Herbers Excel-Forum


Betrifft: Letzte belegte Zelle mit VBA von: Herbert Grom
Geschrieben am: 12.02.2012 16:08:12

Hallo,

in einer Spalte habe ich in den Zellen von Zeile 4 - 53 Formeln stehen, die, je nach Bedingung einen Wert anzeigen, oder eben nichts mit "". Immer eine Zeile nach der anderen. Nun will ich per VBA die Zeile ermitteln, in der der letzte sichtbare Wert steht. Mit "iLastRow = Cells(Rows.Count, 1).End(xlUp).Row" wird mir nur die Zeile mit der letzten Formel angezeigt = 53. Ich stehe irgendwie auf dem Schlauch. Könnt Ihr mir bitte die Lösung sagen? Dafür vielen Dank im Voraus.

Servus

  

Betrifft: AW: Letzte belegte Zelle mit VBA von: Josef Ehrensberger
Geschrieben am: 12.02.2012 16:20:10


Hallo Herbert,


Sub test()
  MsgBox FirstEmptyCell(Range("B4:B53")).Address
End Sub



Public Function FirstEmptyCell(Target As Range) As Range
  Dim vntRet As Variant, strRef As String
  
  With Target
    strRef = "'" & .Parent.Name & "'!" & .Address
    vntRet = Evaluate("MIN(IF(" & strRef & "="""",ROW(" & strRef & _
      ")+COLUMN(" & strRef & ")*10^-6))")
    If IsError(vntRet) Or vntRet = 0 Then Exit Function
    Set FirstEmptyCell = .Cells(Clng(Split(vntRet, ",")(0)) - .Rows(1).Row + 1, _
      Clng(Split(vntRet, ",")(1)) - .Columns(1).Column + 1)
  End With
End Function






« Gruß Sepp »



  

Betrifft: For x = 4 To 53 ... von: Matthias L
Geschrieben am: 12.02.2012 16:20:13

Hallo

Für diesen kleinen Bereich kannst Du das so machen

Dim x&, MyRow&
For x = 4 To 53
 If Cells(x, 1).Value <> "" Then MyRow = Cells(x, 1).Row
Next
MsgBox MyRow
Gruß Matthias


  

Betrifft: Vielen Dank ihr 2 von: Herbert Grom
Geschrieben am: 12.02.2012 16:33:55

aber das ist nicht das was ich suche. Das ist viel zu umständlich und aufwändig. Vielleicht gibt es ja noch einen "Einzeilenlösung".

Servus


  

Betrifft: Sorry, mein Level ist < als Dein Level ... von: Matthias L
Geschrieben am: 12.02.2012 17:21:46

Hallo

Dann lass die Schleife rückwärts laufen

Option Explicit
Sub DasIstNichtKompliziert()
Dim x&, MyRow&
For x = 53 To 4 Step -1
 If Cells(x, 1).Value <> "" Then MyRow = Cells(x, 1).Row: Exit For
Next
MsgBox MyRow
End Sub
Wenn das zu umständlich und zu aufwändig ist für Dich, bin ich raus.

Gruß Matthias


  

Betrifft: AW: Sorry, mein Level ist < als Dein Level ... von: Herbert Grom
Geschrieben am: 12.02.2012 17:29:33

Hallo Matthias,

bitte nicht böse sein, aber die for - next-Schleife ist mir für meine Anforderung zu Zeitintensiv. So meinte ich das. Sonst ist Deine Lösung schon OK.

Servus


  

Betrifft: was ist daran umständlich? o.T. von: Josef Ehrensberger
Geschrieben am: 12.02.2012 17:24:41

« Gruß Sepp »



  

Betrifft: AW: was ist daran umständlich? o.T. von: Herbert Grom
Geschrieben am: 12.02.2012 17:31:55

Hallo Sepp,

im meinem Tool habe ich schon einmal mit einer UDF gearbeitet. Da ist er dann ständig hingesprungen, ohne dass es eine Veranlassung dazu gab. Und das kostet eben Zeit. Das meinte ich mit Umständlich.

Vielen Dank!

Servus


  

Betrifft: AW: was ist daran umständlich? o.T. von: Josef Ehrensberger
Geschrieben am: 12.02.2012 17:41:03


Hallo Herbert,

wenn was "springt", dann hängt das wohl mit deinem Code zusammen.




« Gruß Sepp »



  

Betrifft: AW: was ist daran umständlich? o.T. von: Herbert Grom
Geschrieben am: 12.02.2012 18:12:09

Hallo Sepp,

das wird schon so sein. Doch hatte ich auch keine Zeit das zu korrigieren. Application.Volatile könnte evtl. auch Abhilfe bringen. Mal sehen!

Servus


  

Betrifft: AW: was ist daran umständlich? o.T. von: Josef Ehrensberger
Geschrieben am: 12.02.2012 18:49:45


Hallo Herbert,

was soll das Application.Volatile bringen? Das verwendet man doch nur bei Tabellenfunktionen.

Vielleicht solltest du mal genauer schildern, was du erreichen willst.




« Gruß Sepp »



  

Betrifft: AW: was ist daran umständlich? o.T. von: Herbert Grom
Geschrieben am: 12.02.2012 18:55:56

Hallo Sepp,

vielen Dank für Dein Interesse und Dein Angebot. Doch es muss jetzt nicht vertieft werden. Die Lösung von Tino ist ja das was ich suchte.

Servus


  

Betrifft: AW: Vielen Dank ihr 2 von: Tino
Geschrieben am: 12.02.2012 17:47:13

Hallo,
wie wäre es mit dieser Variante, gibt die letzte Zeile zurück die einen Wert <>"" ausgibt zurückt.

Dim lngLetzte
lngLetzte = [=LOOKUP(2,1/(Tabelle1!A:A<>""),ROW(A:A))]

Gruß Tino


  

Betrifft: AW: Vielen Dank ihr 2 von: Herbert Grom
Geschrieben am: 12.02.2012 18:10:26

Hallo Tino,

wieso erhalte ich da einen "Fehler 2036"?

Servus


  

Betrifft: AW: Vielen Dank ihr 2 von: Herbert Grom
Geschrieben am: 12.02.2012 18:25:26

Hallo Tino,

nun klappts! Ich habe den Abfragebereich eingegrenzt ("Q1:Q48<>""),ROW(Q1:Q48)") und nun klappt es! Vielen Dank!

Servus


  

Betrifft: AW: Vielen Dank ihr 2 von: Tino
Geschrieben am: 12.02.2012 18:42:13

Hallo,
ich würde noch die Tabelle dazuschreiben solltest Du dich mal auf einer anderen befinden
und besser gleich mit Hochkomma dann gibt es keine Probleme wenn der Tabellenname ein Leerzeichen enthält.

lngLetzte = [=LOOKUP(2,1/('Tabelle1'!Q1:Q48<>""),ROW('Tabelle1'!Q1:Q48))]

Gruß Tino


  

Betrifft: AW: Vielen Dank ihr 2 von: Herbert Grom
Geschrieben am: 12.02.2012 18:46:34

Hallo Tino,

danke für den Tipp, doch das habe ich natürlich gemacht! Aber wenn Du schon gerade noch mal "dran" bist, könntest Du mir bitte noch die beiden Parameter "2,1" erläutern, damit ich sicher bin, ob ich es auch richtig verstanden habe?

Servus


  

Betrifft: AW: Vielen Dank ihr 2 von: Tino
Geschrieben am: 12.02.2012 18:56:02

Hallo,
die Formel stammt von hier
http://www.excelformeln.de/formeln.html?welcher=48
Habe sie nur mit Evaluate (entspricht den eckigen Klammern) im VBA konvertiert. (siehe in der VBA Hilfe)

Gruß Tino


Beiträge aus den Excel-Beispielen zum Thema "Letzte belegte Zelle mit VBA"