Office: VBA/ Makro Zelle löschen

Hallo,

das letzte mal mit VBA habe ich im der Schulzeit gearbeitet und das is nun 10 Jahre her. Vlt könnt Ihr mir bei meiner (ich denke mal einfachen) Frage helfen.

Folgende Tabelle ist meine Grundlage

[table="width: 500, class: grid"]
[tr]
[td]Pos[/td]
[td]Menge[/td]
[td]Bezeichnung[/td]
[td]Preis[/td]
[td]Gesamt[/td]
[/tr]
[tr]
[td]1[/td]
[td]10[/td]
[td]Schuh[/td]
[td]10 €[/td]
[td]100 €[/td]
[/tr]
[tr]
[td][/td]
[td]2[/td]
[td]Sohle[/td]
[td]1 €[/td]
[td]2 €[/td]
[/tr]
[/table]

Jeder "Hauptartikel" hat Unterpositionen, welche eingeklappt sind. In der Zelle B3 (Menhe Sohle) steht Formel =B2*2 (da man ja für ein paar Schuhe - zwei Sohlen brauch). Die Tabelle geht nun mit anderen Hauptartikeln weiter, welche wieder Unterpositionen mit Formeln besitzt. Wenn ich die Mengenspalte komplett auswähle (Unterpositionen eingeklappt/gruppiert) und lösche, löscht es mir ja die formeln der Unterpositionen weg. Ich bin also auf der suche nach einem makro, dass prüft ob eine Positionszahl vergeben ist, und dann in dieser Zeile nur die Menge löscht.

Ich denke Ihr könt mir da schnell weiterhelfen.

Vielen Dank im Voraus!
Fetl

 

Moinsens!

Mal so vom groben Aufbau her: Du brauchst eine Schleife, die die gesamte Tabelle durchläuft. Schleifen gibt es ja, wie Du Dich vielleicht erinnerst, in mehreren Varianten. For..Next, While..Wend, ... Wenn ich das richtig verstanden habe, willst Du ja nur den Zellinhalt, nicht die Zelle (Zeile, Spalte) selbst löschen. Es verschiebt sich durch das Löschen also nicht der Bereich. Von daher würde eine For..Next problemlos gehen. Wäre nur noch zu klären, von wo bis wo sie läuft.

B3: =B2*2... Hmmm... Wieso steht da bei Dir eine 2, wenn in B2 eine 10 steht? Aber ok, egal... Es soll geprüft werden, ob in Spalte A eine Positionszahl steht, um dann in Spalte B die Menge zu löschen. Umgekehrt: Steht in Spalte A nix, soll die Formel nicht gelöscht werden. richtig?

Gut, zunächst brauchst Du die Information für das Schleifen-Ende, also bis zu welcher Zeile die Tabelle reicht. Nimm Dir dazu eine Spalte, die garantiert mit einem Wert gefüllt ist, mindestens in der letzten Zeile der Tabelle. Ich tippe mal auf Spalte B, da dort ja die Formeln stehen.

Code:

    letztezeile = s1.cells(s1.rows.count,2).end(xlup).row 'die "2" ist spalte "b"

Damit haben wir schon mal die Schleife:

Code:

    for y=2 to letztezeile 'ab zeile "2" bis tabellen-ende
        ' ...
    next y

Und was passiert innerhalb der Schleife? Test auf Positionsnummer:

Code:

        if (trim$(s1.cells(y,1).value)<>"") then 'die "1" ist spalte "a"
            ' ...
        endif

Und jetzt nur noch die eigentliche Aktion:

Code:

            s1.cells(y,2).clearcontents 'löscht nur inhalt, behält formatierung

Ein paar Deklarationen vorweg:

Code:

private sub mengeloeschen()

    dim s1 as worksheet
    dim letztezeile as long
    dim y as long

    set s1=activeworksheet

    ' ...

end sub

Und dann sollte es eigentlich funktionieren. Wäre jetztt nur noch zu klären, wann (und dadurch: wie) Du diese Routine aufrufst. Wenn Du einen Button auf das gleiche Tabellenblatt legst, kannst Du mittels

Code:

    call mengeloeschen()

die o.a. Routine aufrufen. Das "call" und die "()" kannst Du auch weglassen...

Code:

private sub mengeloeschen()

    dim s1 as worksheet
    dim letztezeile as long
    dim y as long

    set s1=activeworksheet
    letztezeile = s1.cells(s1.rows.count,2).end(xlup).row 'die "2" ist spalte "b"

    for y=2 to letztezeile 'ab zeile "2" bis tabellen-ende
        if (trim$(s1.cells(y,1).value)<>"") then 'die "1" ist spalte "a"
            s1.cells(y,2).clearcontents 'löscht nur inhalt, behält formatierung
        endif
    next y

end sub

Ist jetzt Trockenschwimmen, aber so müßte es eigentlich gehen...

Gruß, Michael

 

Hallo miriki,

erstmal vielen Dank für Deine Mühe....wie sagt man - alles zur Übung...nichts zur Strafe -.-'
ich hab die ganze zeit weitergesucht und probiert. Letztendlich bin eben ich auf ne simple Lösung gestoßen, die meine Frage auf den ersten Blick Problemlos beantwortet

Sub Formular_loeschen()
Range("C10:D300").SpecialCells(xlCellTypeConstants) = ""
End Sub

Ich bin ehrlich...ich hab keine Ahnung wie diese Formel funktioniert....aber sie ignoriert die Zellen mit den Formeln und löscht nur die manuell eingetragenen Felder/Zellen.

Was mir für diese Tabelle nur noch fehlt wäre folgendes:

Die Datei besteht aus 2 Tabellenblättern.
In einem Tabellelblatt sind alle Konfigurationen festgelegt. Über einen Button werden Daten aus einer Datenbank ausgelesen und in Tabelle 2 den entsprechenden Positionen zugeordnet. Diese Zuordnung Basiert auf Baugruppennummern.

Die Formel hat nur einen Nachteil - Wenn ein Artikel keine Baugruppennummer besitzt, können auch keine Werte nicht ausgelesen werden. Seine Werte (Material, Sub und Lohn) werden in der Tabelle 2 dennoch gelöscht bzw. diese Zellen geleert.

Ich habe mir überlegt, dass man doch im VBA vor der "Ausleseformel" schreiben könnte : " wenn in der Spalte keine Nummer vorhanden, dann mache nichts in der Zeile, wenn eine vorhanden ist, dann führe den vorgegebenen Vorgang aus und Füge die Daten entsprechend ein"
...also "einach" eine Bedingung davorsetzten.

Dabei müsste ich aber beachten, dass der Button im Tabellenblatt 1 liegt und die werte in Tabelle 2 eingefügt werden.

ODER:

man könnte diese Artikel (ohne Baugruppe) in einer Hilfsspalte/Hilfstabelle zusammenfassen und eine Formel erzeugen, welche die Artikelbezeichung in der Zieltabelle 2 sucht und mit der Hilfstabelle vergleicht. Ist der Wert in beiden vorhanden, so soll er die Spalte G, H und J mit den entsprechenden werten aus der Hilfstabelle ausfüllen (quasi wie ein sverweis)

Kannst Du mir vlt. hier noch weiterhelfen?

Vielen Dank im Voraus!

 

Moinsens!

Ach, das ist relativ einfach: Im angegebenen Bereich, also C10 bis D300 in Deinem Fall, werden alle Zellen gesucht (SpecialCells), die nicht mit einer Formel, sondern mit einem festen Wert (xlCellTypeConstants) belegt sind. Ich kann mich gar nicht erinnern, ob und wann ich das Ding mal benutzt habe. Aber: Das Ding löscht Dir wirklich _alles_, was keine Formel hat. Damit wären auch Texte wie z.B. (Zwischen-) Überschriften oder manuell gesetzte Faktoren für die Berechnung weg.

Solange das für Dich kein Problem ist, alles gut. Mir wäre diese Methode etwas zu "brutal" und mir würde etwas die Kontrolle bei dem Löschvorgang fehlen. Aber einen ganz enormen Vorteil hat es: Es ist brandschnell!

Wieso fängst Du erst in Zeile 10 an? Dein Beispiel fing eigentlich bereits in Zeile 2 an... Und woher weißt du, daß in Zeile 300 Schluß ist?

(Genau genommen ist das übrigens keine Formel, sondern ein VBA-Befehl - noch etwas genauer: Eine Methode des Range-Objekts.)

Das hängt davon ab, wie die "Daten ausgelesen und angehängt" werden. Geschieht dies Zeilen- / Datensatz-weise, kann man in jeder Zeile eine entsprechende Prüfung einbauen. Das wäre dann wirklich nur eine simple If..EndIf Klammerung.

Geschieht es aber im "Batch" und alle Datensätze werden auf einen Schlag übertragen, ist's damit Essig. Dann kann man nur im Nachhinein reparieren.

Auch die Idee mit der temporären Hilfstabelle ist vielleicht möglich. Aber da ich absolut nicht weiß, was da genau passiert, ist das wildes Herumgerate.

Gruß, Michael

 

Hi,

das ganze beginnt erst in Zeile 10, weil sich darüber Kopfzeilen, Überschirften, Spaltenbezeichnungen etc. stehen., naja und die 300 habe ich willkürlich gewählt - hätte auch 3000 sein können, aber ich geh davon aus, das für die geplante Verwendung nicht mehr Zeilen benötigt werden.
Eleganter wäre sicherlich ein Befehl / Formel, welche die komplette Spalte prüft und löscht, aber wie gesagt - meine VBA Kenntnisse beschränken sich auf die Schulzeit und zwischenzeitlich hab ich mich mit C&P ganz gut über Wasser halten können (auch wenn ich gern mehr Verstehen würde).

ich möchte den Code ungern hier freigeben, da ich Ihn nicht erstellt habe etc. Darum denke ich auch, dass es nich so leicht sein wird diese IF...EndIF Geschichte einzupflegen.

Deine Argumentation bezüglich "meines" Codes klingt schlüssig - ich werd nochmal versuchen Deinen auf meine Bedürfnisse anzupassen. Vielen Dank nochmal!


Zur anderen Thematik - Wie wäre es damit:
ich hab ja 2 Tabellenblätter.
in Tabellenblatt 2 werden alle Daten eingespielt (durch betätigung des Buttons in Tabellenblatt 1). Hier lege ich eine Hilfsspalte an, in der ich mit einem simplen "X" die Zeilen manuell markiere, welche beim Auslesen der Daten geleert werden.

In Tabellenblatt 1 hingegen lege ich eine kleine Tabelle an, in der die bezeichnung dieser Positionen sowie die dazugehörigen, wiederherzustellenden Werte dargelegt sind. Daneben ein Button.
Dieser Button (muss am Besten in Tabellenblatt 1 liegen, da der andere sich auch hier befindet) soll nun im Tabellenblatt 2 prüfen, in welcher Zeile der Hilfsspalte ein "X" steht und dann in z.B. G, H und J eine Sverweis-Formel eintragen. Diese wird nun die Artikelbezeichnungen der Beiden Tabellenblätter prüfen und die geforderten Werte in die entsprechenden Spalten einfügen. Dafür ist es sicher notwendig, das 3 Schleifen (also für jede Spalte eine Schleife) geschrieben wird-richtig?

ODER...ist es einfacher direkt über den Button zu sagen....wenn X..dann Artikelbezeichnung vergleichen und in Spalte G den passenden Wert, in H den Passenden Wert und J ebenso einfügen?

Gruß, Frank

 

Hallo nochmal,

ich habe mir mal bisschen was zusammengebastelt, das recht gut klappt...ich hab nur ein Problem mit folgendem Befehl:

zu folgendem Befehl:

es soll die Spalte L in Tabelle 2 mit der Spalte H der Tabelle 1 verglichen werden. Wenn die Werte übereinstimmen, sollen die werte aus Spalte I, J, und K aus Tab1 in die Spalten H, I und J (Tab2) hineinkopiert werden. Ich denke das könnt Ihr aus dem Befehl heraslesen.

Sub Schaltfläche2_Klicken()

Dim rngQuelle As Range
Dim rngZiel As Range

With ThisWorkbook.Sheets("VK-Preise")
For Each rngZiel In .Range("L1:L" & .Cells(.Rows.Count, 6).End(xlUp).Row)
On Error Resume Next
If Zelle.Value <> "" Then
Set rngQuelle = ThisWorkbook.Sheets("Konfiguration").Range("H:H").Find(What:=rngZiel)
On Error GoTo 0
If Not rngQuelle Is Nothing Then
rngQuelle.Offset(0, 1).Resize(1, 3).Copy
rngZiel.Offset(0, -4).Resize(1, 3).PasteSpecial Paste:=xlPasteValues
End If
End If
Next 'rngZiel
End With

End Sub

Die Problematik:

1. er läuft sehr langsam
2. wenn die Zelle in Spalte L leer ist, werden leere Zellen eingefügt, das heißt - in diesem Fall werden in den Spalten H, I und J bereits vorhandene Werte gelöscht oder die Zelle geleert

Ich würde also eine Lösung benötigen, die im Falle einer Leeren Zelle, einfach die nächste prüft und nichts tut. --> ich denke, das würde die ganze Sache auch beschleunigen.

Vielen Dank im Voraus!

Gruß Frank

 

Moinsens!

Das dürfte primär an der .Find-Methode liegen. Da gäb's vielleicht eine Alternative, aber ich bin mir nicht sicher, ob die schneller wird.

Naja, das ist ja nur eine Frage, was Du innerhalb If .. Else .. Then anstellst. Du fragst ja nach dem .Find bereits ab, ob es erfolgreich war - nur dann wird was kopiert. Du könntest also direkt danach noch abfragen, ob die zu kopierenden Zellen leer sind - dann wird eben doch nicht kopiert.

Du könntest alternativ versuchen, mittels sVerweis die Werte zu "kopieren". Dazu könntest Du eine Formel am Anfang der Liste erstellen, diese dann "runterziehen" und danach mit copy / paste durch konstante Werte ersetzen. Dabei stößt Du aber auch wieder auf das "leere Zellen" Problem.

Du könntest den sVerweis in einer Hilfs-Spalte aufbauen und dann nur die Werte ans endgültige Ziel kopieren, die nicht leer und nicht #NV sind.

Gruß, Michael