Zeilen/Zellen ohne Werte ausblenden mit VBA

[harye]

Hallo Forummitglieder,

ich möchte gerne mich näher mit Excel und eventuell VBA beschäftigen, aber habe bis nur Grundwissen. Allerdings brauche ich für eine "Automatisierung " einer Tabelle eine Lösung und ich glaube ich arbeite logisch falsch. Aber sehr selbst.

Folgender Sachverhalt

Ich übertrage bestimmte Daten - unter Erfüllung einer bestimmten Voraussetzung (ja/nein) - mit einem SVERWEIS von Tabellenblatt1 auf Tabellenblatt2. 

Soweit so gut.

Jetzt habe ich als ID, welche den SVERWEIS bestimmt, eine fortlaufende Nummer in beiden Tabellenblättern die gleich ist. Diese sind aber nicht entscheidend für den weiteren Gebrauch, sondern dienen als Hilfsspalte und sind im "Betrieb" ausgeblendet.

Also:

1
2
3
4
usw.

Wenn nun aber die Voraussetzung in Tabellenblatt1 nicht erfüllt wird (also "nein"), sind die Daten folglich auch nicht in Tabellenblatt2 übertragen (was ja auch so gewollt ist!!!)--> ABER somit habe ich dort eine Zeile und Werte (Leerzeile), durch die fortlaufende Nummerierung der Hilfsspalte und es ergebn sich Lücken zwischen den Datensätzen.

Und genau das möchte ich nicht! :16: 

Es soll wie folgt aussehen:

Tab1                     Tab2
1 nein                   3 ja
2 nein                   5 ja
3 ja                       usw.        
4 nein
5 ja
6 nein
usw.

1. Gibt es eine Lösung die bspw. sagt:

Schaue in die letzte beschriebene Zeile und blende alle "leeren Zeilen" (Formeln sind ja drin vorhanden, nur eben keine Werte) darüber aus?

???  ???

2. Oder gibt es eine schlauere Lösung bei der Übertragung von Blatt1 auf Blatt2, sodass erst gar keine Leerzeilen entstehen?

Ich hoffe ich konnte meine Problemstellung verständlich erläutern und bin für jeden Hinweis dankbar!!!

Danke schon mal  :17:

Harye

[schauan]

Hallöchen,

wenn Du Deine Ergebnisspalte als Hilfsspalte nimmst, kannst DU z.B. mit dieser Lösung
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=43
oder
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=160
eine weitere Tabelle ohne Leerzeilen erzeugen.

Leerzeilen kannst Du mit dem Filter ausblenden. Den findest Du oben im Reiter "Daten".

[harye]

Hallo Schauan,

danke für die schnelle Antwort.

Prinzipiell ist die Idee mit den Ergebnis-Hilfsspalten denkbar - nur scheitere ich trotz deiner Hilfe an der Umsetzung. Ich habe nun mal eine Beispieldatei mit hinzugefügt und dort auch bereits die Formeln angepasst ABER es funktioniert nicht. Kannst du den Fehler sehen und/oder mir vll. eine Hilfestellung geben?

Vielen Dank schon mal.

Harye.

[schauan]

Hallöchen,

meine nächste Antwort dauert leider etwas länger. Gehe jetzt offline und bin dann erst mal 3 Tage nur per Handy im Forum. Würde mich dann Freitag weiter um das Thema kümmern, falls Du bis dahin nicht von einem anderen user geholfen bekommst..

[BoskoBiati]

Hallo,

Deine Formeln taugen nichts und die Datei gibt auch nicht viel her!

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Hilfsspalte Datum	Datum	Hilfsspalte Ware	Ware	Hilfsspalte Lieferant	Lieferant
2	01.01.2016	01.01.2016	Bananen	Bananen	Meier	Meier
3	15.02.2016	15.02.2016	Äpfel	Äpfel	Müller	Müller
4		18.03.2016		Kirschen		Schulz
5	18.03.2016	20.04.2016	Kirschen	Pflaumen	Schulz	Schmidt
6
7	20.04.2016		Pflaumen		Schmidt

Zelle	Formel
B2	{=WENNFEHLER(KKLEINSTE(WENN(A$2:A$10<>0;$A$2:$A$10);ZEILE(A1));"")}
D2	{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(C$2:C$10<>"";ZEILE($2:$10));ZEILE(A1)));"")}
F2	{=WENNFEHLER(INDEX(E:E;KKLEINSTE(WENN(E$2:E$10<>"";ZEILE($2:$10));ZEILE(A1)));"")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Gast 123]

Hallo harye,

bis jetzt sprecht ihr nur über Formel Lösungen, Daten kann man auch per VBA auswerten und kopieren.
Mich interessieren dabei keine Lücken zwischen den Daten, das kann man in der Ziel Tabelle lückenlos auflisten.

Ich muss nur wissen wo die Daten stehen, brauche Tabellen Name, Spalten, Zeilen, Überschriften, nach welchen
Kriterien Daten zu kopieren sind, und in weiche Tabelle in welche Spalte ab welcher Zeile  kopiert werden soll.
 Den Rest erledigt das Makro. Am besten ist eine Beispieldatei mit anonymen Daten als Quell Datei und eine
Beispiel Tabelle wie das Ergebnis aussehen soll.  VBA braucht in der Regel keine Hilfsspalten. 

Gast 123

[harye]

Erstmal vielen Dank für die Antworten!

Hier noch einmal eine Beispieldatei im Anhang.

Quelldatei ist Arbeitsblatt "Start". Das Arbeitsblatt "Ziel" stellt da wie es letztlich aussehen soll.

Was genau gemacht werden soll ist in "Start" noch einmal vermerkt. 

Ich hoffe es ist jetzt etwas klarer.

[harye]

Ps: Wenn die zu erfüllende Bedingung, also "2", erst zeitlich später eingetragen wird (nachdem bspw. schon 30 weitere Zeilen darunter beschrieben wurden), funktioniert dann das Makro auch?

Soll heißen: tauchen die Daten dann trotzdem im Blatt "Ziel" auf?



Kannst du - Gst 123 - eventuell die einzelnen Schritte im Editor beschreiben, damit ich auch verstehe und lernen kann was genau das makro bzw. welche Syntax macht?

Danke dir

[Gast 123]

Hallo harye

anbei ein kleines Makro als Lösung für diese Aufgabe. Du wirst vielleicht überrascht sein wie kurz es ist.
Anfänger erwarten immer -Range.Select.-  Wenn du selbst programmieren kannst vermeidest du es.
Schau es dir in Ruhe an. Für die Profis im Forum ist das banaler Kinderkram. Du wirst eine ganze Weile
brauchen bis du es in allen Funktionen verstanden hast. Hauptsache es funktioniert.

mfg  Gast 123

Code:

Option Explicit      '26.4.2016   Gast 123 für Clever Forum

Const ClrZiel = "B3:J1000"    'Lösch Bereich Ziel Tabelle


'mit Syntax Erklärung:
'lz = LastCell in Spalte B abwärts suchen
' z = fortlaufende Zelle in Ziel Tabelle
'AC = ActiveCell als Objekt  Cells ~~ Range
'For Each durchläuft alle Zellen in Spalte N


Sub Daten_auswerten_und_Kopieren()
  Dim Start As Object, Ziel As Object
  Dim AC As Object, lz As Long, z As Long
  Set Start = Worksheets("Start")
  Set Ziel = Worksheets("Ziel")
 
  'alte Ziel Tabelle löschen
  Ziel.Range(ClrZiel).ClearContents
 
  'letzte Zelle ermitteln  (abwärts)
  lz = Start.Range("B3").End(xlDown).Row
  If lz = Cells.Rows.Count Then Exit Sub
 
  z = 3  '1. Zeile in Ziel Tabelle
  'Schleife zum Werte prüfen in Spalte "N-R"
  For Each AC In Start.Range("N3:N" & lz)
     If AC.Value = 2 Or AC.Cells(1, 3) = 2 _
         Or AC.Cells(1, 5) = 2 Then
        'wenn Wert=2 Spalte B-J kopieren
        Start.Cells(AC.Row, "B").Resize(1, 9).Copy
        Ziel.Cells(z, "B").PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        z = z + 1 'nächste Zeile in Ziel Tabelle
     End If
  Next AC
End Sub

'**  Cells und Range adressieren Zellen, sind aber nicht gleich
'**  Resize(1,x) beeinflusst den selektierten Bereich nach rechts

[BoskoBiati]

Hallo,

hiermit geht es wahrscheinlich schneller:

Code:

Option Explicit

Sub übertragen()
   Dim rng As Range
   Dim loLetzte As Long
   Dim shStart As Worksheet
   Dim shZiel As Worksheet
   Set shStart = Sheets("Start")
   Set shZiel = Sheets("Ziel")
   shZiel.Range("B3:J10000").Clear
   With shStart
      .Rows(2).AutoFilter field:=14, Criteria1:=2
      .Range("B3:J10000").SpecialCells(xlCellTypeVisible).Copy shZiel.Range("B3")
      .ShowAllData
      loLetzte = shZiel.Cells(Rows.Count, 2).End(xlUp).Row + 1
      .Rows(2).AutoFilter field:=16, Criteria1:=2
      .Range("B3:J10000").SpecialCells(xlCellTypeVisible).Copy shZiel.Range("B" & loLetzte)
      .ShowAllData
      loLetzte = shZiel.Cells(Rows.Count, 2).End(xlUp).Row + 1
      .Rows(2).AutoFilter field:=18, Criteria1:=2
      .Range("B3:J10000").SpecialCells(xlCellTypeVisible).Copy shZiel.Range("B" & loLetzte)
      .ShowAllData
      .EnableAutoFilter = False
   End With
   With shZiel
      .Range("B2:J10000").Sort key1:=.Range("B3"), order1:=xlAscending, Header:=xlYes
   End With
End Sub