Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Erste leere Zeile in Bereich finden

Erste leere Zeile in Bereich finden
01.10.2013 22:32:39
Dietmar
Hallo in die Runde,
ich weiß, dass diese Thema schon häufiger angegangen wurde, finde aber nicht den für mich zutreffenden Ansatz.
Ich möchte Daten kopieren und in einen anderen Bereich kopieren; wobei der Code in dem Bereich die erste (komplett)freie Zeile finden soll, in den dann die Werte eingetragen werden sollen.
Was soll geschehen?
Kopiere Sheets("Tabelle1").Range("B6:E6")
Gehe zu Sheets("Tabelle2") und prüfe dort zunächst im Datenbereich Range("B2:E100")
in welcher Zeile (im Bereich B5:E200) noch kein Wert steht. Wenn in irgendeiner Zelle dieses Bereichs ein Wert steht, soll die nächste komplett frei Zeile angesteuert werden und der Übertrag in diese Zeile erfolgen.
Habe mal eine Datei als Erklärung begefügt:

Die Datei https://www.herber.de/bbs/user/87493.xls wurde aus Datenschutzgründen gelöscht


Vielen Dank für einen Tipp/Code.
LG
Dietmar aus Aachen

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Das sieht gut aus ...
02.10.2013 18:06:37
Dietmar
Hallo Franc,
kurz, schmerzlos, zielführend!
Vielen Dank, genau so hatte ich es mir vorgestellt!
LG
Dietmar,

Erste leere Zeile in Bereich
02.10.2013 10:29:13
Erich
Hi Dietmar,
hier noch eine Alternative: Option Explicit Sub Werte_in_freie_Zeile_einfuegen2() Dim lngZ As Long With Sheets("Tabelle2") lngZ = NextFreeRow(.Range("B4:E100")) ' Zielbereich If lngZ > 0 Then .Cells(lngZ, 2).Resize(, 4) = _ Sheets("Tabelle1").Cells(6, 2).Resize(, 4).Value Else MsgBox "Keine Zeile mehr frei - Abbruch" End If End With End Sub Function NextFreeRow(rngB As Range) As Long ' Nr. der Zeile nach der letzten beschriebenen Zeile des Bereichs rngB ' liefert 0, wenn die letzte Zeile des Bereichs nicht leer ist Dim rngC As Range With rngB Set rngC = .Find("*", .Cells(1), xlValues, , xlByRows, xlPrevious) If rngC Is Nothing Then NextFreeRow = .Row ElseIf rngC.Row = .Row + .Rows.Count - 1 Then NextFreeRow = 0 Else NextFreeRow = rngC.Row + 1 End If End With End Function Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Bestens ...
02.10.2013 18:09:36
Dietmar
Hallo Erich,
herzlichen Dank.
Ich werde den Code mal mit dem von Franc testen und schauen, ob es Geschwindigkeits unterschiede gibt.
Bin mal gespannt.
LG
Dietmar

noch ein Bsp mit Do Loop ...
02.10.2013 11:45:54
Matthias
Hallo Dietmar
Hier noch eine Variante mit Formel in Spalte(A)
Dann mit Do ... Loop die erste freie Zelle in Spalte(A) ermitteln.
Zusätzlich eine Möglichkeit zu wählen, ob nur Werte oder auch Formate
übertragen werden sollen. Auch ein Abbrechen ist möglich.
Tabelle2

 ABCDE
4x= belegtEintrag 1Eintrag 2Eintrag 3Eintrag 4
5x1,0   
6x 3,03,0 
7x   4,0
8x   4,0
9x 4,0  
10     
11     
12     
13     

Formeln der Tabelle
ZelleFormel
A5=WENN(ANZAHL2(B5:E5)>0;"x";"")
A6=WENN(ANZAHL2(B6:E6)>0;"x";"")
A7=WENN(ANZAHL2(B7:E7)>0;"x";"")
A8=WENN(ANZAHL2(B8:E8)>0;"x";"")
A9=WENN(ANZAHL2(B9:E9)>0;"x";"")
A10=WENN(ANZAHL2(B10:E10)>0;"x";"")
A11=WENN(ANZAHL2(B11:E11)>0;"x";"")
A12=WENN(ANZAHL2(B12:E12)>0;"x";"")
A13=WENN(ANZAHL2(B13:E13)>0;"x";"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Dim Zeile, Antwort
Zeile = 5
With Tabelle2
Do While .Cells(Zeile, 1).Value  Empty
Zeile = Zeile + 1
Loop
End With
Antwort = MsgBox("Eintrag erfolgt in Zeile " & Zeile & vbLf & "Sollen auch die Formate mit ü _
bertragen werden?", vbYesNoCancel)
If Antwort = vbYes Then
Tabelle1.Range("Bereich").Copy Tabelle2.Cells(Zeile, 2)
ElseIf Antwort = vbNo Then
Tabelle1.Range("Bereich").Copy
Tabelle2.Cells(Zeile, 2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
'mach nix
End If
Hier sollte nun nur noch ein Ende festgelegt werden bei welcher Zeile
definitiv die Schleife verlassen werden soll.


Tabelle1.Range("B6:E6") hab ich im Beispiel als Name:=Bereich definiert,
Da ich viel mit definierten Namen arbeite, ist das nur eine Gewohnheit.
Dieser Vorschlag nimmt wirklich die erste freie Zeile,
auch wenn z.B. zwischen dem letzten und vorletzten Eintrag Leerzeilen vorhanden sind.
Dann wird nicht unter dem letzten Eintrag eingefügt, sondern tatsächlich in der ersten freien Zeile.

Bsp wie ich das meine:


Tabelle2

 ABCDE
4x= belegtEintrag 1Eintrag 2Eintrag 3Eintrag 4
5x1,0   
6x 3,03,0 
7x   4,0
8x   4,0
9x 4,0  
10     
11     
12     
13     
14xZeilebittefreilassen 
15     


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Hier wird in Zeile(10) eingefügt nicht erst in Zeile(15)
Hast nun 3 funktionierende Möglichkeiten.
Du kannst nun selbst entscheiden.
Auch ich würde mich über eine Rückmeldung freuen.
Gruß Matthias

Anzeige
So hatte ich es anfangs auch vor ...
02.10.2013 18:16:59
Dietmar
Hallo Matthias,
vielen Dank.
So war meine anfängliche Idee auch gewesen :-)
Hatte dann aber einerseits mit dem Code Probleme. Und ferner fehlt mir in dem weiter zu entwickelnden Sheet dazu der Platz.
Kann ich aber sehr gut für ein anderes Projekt gebrauchen.
LG und einen schönen Feiertag :-)
Dietmar

noch ein paar Schleifchen ;-)
02.10.2013 13:26:12
Erich
Hi Dietmar,
hier wird in die erste freie Zeile übertragen wie bei Matthias' Code:

Option Explicit
Sub Werte_in_freie_Zeile_einfuegen()
Dim arQ, zz As Long, cc As Long
With Sheets("Tabelle2")
arQ = .Range("B4:E100")             ' Zielbereich
For zz = 1 To UBound(arQ)
For cc = 1 To UBound(arQ, 2)
If Not IsEmpty(arQ(zz, cc)) Then Exit For
Next cc
If cc > UBound(arQ, 2) Then Exit For
Next zz
If zz 
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Wow ...
02.10.2013 18:22:15
Dietmar
Hallo Erich,
lieben Dank auch für diese Option.
Na, da kommt ja wohl auch nur der Profi drauf!
Ich stelle auch diesen Code mal den anderen gegenüber und schau mal was schneller ist.
Da ist nämlich für mein Projekt durchaus wichtig, weil da im Vorfeld noch eine ganze Reihe anderer Aktionen (Codes) ablaufen.
Da ist es schon toll, dass ich so eine große Auswahl habe :-)
So, nun auch Dir einen schönen Feiertag morgen.
LG
Dietmar

Frage beantwortet aber ...
03.10.2013 19:38:31
Dietmar
Hallo Ericht,
ich habe nun alle Varianten durchgecheckt und dieser Code von Dir funzt am schnellsten.
Nun möchte ich ihn etwas verändern, scheitere aber, da ich die Stellschrauben nicht so richtig verstehe :-(
Was soll geändert werden:
a) In Tabelle 1 soll der zu kopierende Bereich sein: B9:B12 (also senkrecht, vorher B6:E6)
b) In Tabelle 2 soll das Ziel nun sein B5:E100 (vorher B4:E100, also eine Zeile weiter runter)
Danke für erneute Hilfe!
LG
Dietmar

Anzeige
Deshalb verwendet man für so etwas und ...
04.10.2013 16:20:09
Luc:-?
…alles, was damit zusammenhängt ja auch Konstanten (bzw Variablen, deren Wert sich aus den jeweiligen Werten der Konstanten ableiten lässt), Dietmar (und Erich);
dann ist eine Änderung ein Klacks! Leider wird das hier nur selten gezeigt!
Gruß Luc :-?

Ok ...
04.10.2013 16:46:24
Dietmar
Hallo Luc,
Du wirst recht haben, damit muss ich mich mal unbedingt befassen! Im Moment hilft mir dieser Hinweis aber nicht weiter.
Ich habe mir ja die CD gekauft. Dann werde ich da mal schauen, ob ich da ein gutes Beispiel finde.
Bis dahin lasse ich den Thread mal 'offen' und hoffe auf einen gnädigen Beitrag, der einem 'Konstant-Variabel-Unwissenden' weiterhilft :-)
LG
Dietmar

Anzeige
Na gut, dann mal dafür die notwendigsten ...
04.10.2013 18:48:40
Luc:-?
…Änderungen, Dietmar:
Sub Werte_in_freie_Zeile_einfuegen()
Const ZlVorschub As Long = 3, adQBer$ = "B6:E6", adZBer$ = "B4:E100"
Dim cc As Long, zz As Long, arQ, relQBer As Range
Set relQBer = Sheets("Tabelle1").Range(adQBer)
With Sheets("Tabelle2")
arQ = .Range(adZBer)             'Zielbereich
For zz = 1 To UBound(arQ)
For cc = 1 To UBound(arQ, 2)
If Not IsEmpty(arQ(zz, cc)) Then Exit For
Next cc
If cc > UBound(arQ, 2) Then Exit For
Next zz
If zz 
Die alten Adressen kannst du jetzt problemlos in der entsprd Konstante durch die neuen ersetzen. Auch der zusätzl Zeilenvorschub kann jetzt leicht geändert wdn.
Mein PgmierStil ist das dadurch aber immer noch nicht. Dazu kannst du ja mal im Archiv nach der UDF VectAr (Vss1.1…2) suchen, die zeigt, wie ich mit ZellBereichen und DatenFeldern (auch aus Ersteren) umgehe.
Gruß Luc :-?

Anzeige
Vielen Dank ...
04.10.2013 20:33:57
Dietmar
Hallo Luc,
damit werde ich mich dann mal beschäftigen und hoffe, dass es irgendwann 'Klick' macht, denn das macht ja wirklich Sinn.
Habe die ähnliche Vorgehensweise bereits über die 'Namen' in Excel schätzen gelernt; das ist ja wohl so etwas ähnliches.
Was mich an dieser Art bisher immer gestört hat, sind die Abkürzungs-Hyieroglyphen, aber ich glaube, da könnte man ja auch anfangs Klartext nehmen, oder?
Vielen Dank also und eine schönes WE!
LG
Dietmar

Kann man auch immer; dito schöWE! ;-)
05.10.2013 02:08:18
Luc:-?
:-?

320 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige