Variable in Range einfügen

Bild

Betrifft: Variable in Range einfügen
von: Rita
Geschrieben am: 01.07.2015 15:16:47

Hallo zusammen,
ich hoffe Ihr könnt mir helfen. Ich bin am verzweifeln.
Ich habe natürlich das Internet zuvor durchsucht und auch super viele Einträge gefunden, doch komme seit über 3 h probieren mit deren Bsp. einfach nicht zurecht.
Ich habe eine Tabelle mit z.B. 5 Zeilen und befüllten Spalten: A-c
ABC
225
326
597
719
353
Ich möchte einen autofilter drüber laufen lassen und habe immer bisher "sicherheitshalber" bis Zeile 1000 laufen lassen, da Zeilenanzahl meiner eingefügten Tabelle stark variieren kann aber A,B,C immer in der gleichen Zeilenzahl enden. Bisher:
ActiveSheet.Range("A1:c10000").AutoFilter Field:=3, Criteria1:=">=6", _
Operator:=xlAnd

Mein Wunsch wäre, dass mir das Makro erkennt wie weit die Tabelle geht (hier am Bsp. 5) und (dann einer Variable zuweisen???) und der autofilter die Variable statt c1000 nimmt. Könntet ihr mir die Syntax an dem Bsp zeigen.
Vielen vielen Dank im Vorraus
Liebe Grüße
Rita

Bild

Betrifft: AW: Variable in Range einfügen
von: Michael (migre)
Geschrieben am: 01.07.2015 15:37:31
Hallo Rita!
Je nachdem, wie Deine Tabelle beschaffen ist (keine Leerzeilen etc.), kannst Du den Autofilter ausgehend von A1 auf den "aktuellen Bereich" (der zu A1 gehört) anwenden, so:

Sub a()
    
    Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=">=6", _
 Operator:=xlAnd
End Sub
So musst Du keine Variable nehmen.
Oder Du speicherst die "letzte Zeile" der gewünschten Spalte (idF C) in einer Variablen so:
Sub b()
    
    Dim leZeile As Long
    
    leZeile = Worksheets("Tabelle1").Range("C1").End(xlDown).Row
    
    ActiveSheet.Range("A1:C" & leZeile).AutoFilter Field:=3, Criteria1:=">=6", _
        Operator:=xlAnd
        
End Sub
Solltest Du allerdings Leerzeilen haben, müsstest Du die letzte befüllte Zeile von zB C "von unten" erfragen und dann in einer Variablen speichern, so:
Sub c()
    Dim leZeile As Long
    
    leZeile = Worksheets("Tabelle1").Range("C1048576").End(xlUp).Row
    
    ActiveSheet.Range("A1:C" & leZeile).AutoFilter Field:=3, Criteria1:=">=6", _
        Operator:=xlAnd
    
End Sub
Kommst Du damit zurecht?
LG
Michael

Bild

Betrifft: AW: Variable in Range einfügen
von: Rita
Geschrieben am: 01.07.2015 16:18:23
Hallo Michael vielen dank für deine schnelle antwort ich werde es gleich morgen früh probieren und dir eine rückmeldung geben
Liebe Grüße Rita

Bild

Betrifft: AW: Variable in Range einfügen
von: Klexy
Geschrieben am: 01.07.2015 16:48:55
Kleiner Hinweis:
Komplett durchgehende Leerzeilen unterbrechen die gefilterte Liste auf jeden Fall. Es wird nur von oben bis zur durchgehenden Leerzeile gefiltert. Alles darunter wird ignoriert.
Da keine zweite Filterbedingung vorhanden ist, kann man es darauf reduzieren:

Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=">=6"
Und eigentlich kann man es auch ganz ohne Angabe des Bereichs machen, weil immer bis zur ersten durchgehenden Leerzeile gefiltert wird. Normalerweise ist das ja das Ende der Tabelle:
Range("A1").AutoFilter Field:=3, Criteria1:=">=6" 
Und sogar so geht es:
Range("B7").AutoFilter Field:=3, Criteria1:=">=6"


Bild

Betrifft: Sorry, aber das stimmt so nicht ganz...
von: Michael (migre)
Geschrieben am: 01.07.2015 17:10:54
Hallo!
Komplett durchgehende Leerzeilen unterbrechen die gefilterte Liste auf jeden Fall. Es wird nur von oben bis zur durchgehenden Leerzeile gefiltert. Alles darunter wird ignoriert.
Das ist mE nicht korrekt: Angenommen im Bereich A2:C24 stehen Daten (wie bei Ritas Bsp., also jeweils Ziffern) aber die Zeilen 9 und 17 sind komplett leer - dann wird bei Verwendung meiner [Sub c()] aus dem o.a. Beitrag sehr wohl der gesamte Bereich in den AutoFilter übernommen, nicht nur bis zur ersten durchgehenden Leerzeile.
Allerdings ist es natürlich sinnvoll, soweit wie möglich zu reduzieren, wie Klexy schreibt.
Schönen Abend Euch!
Michael

Bild

Betrifft: AW: Sorry, aber das stimmt so nicht ganz...
von: Klexy
Geschrieben am: 01.07.2015 17:27:55
Hi Michael,
Wie soll das gehen? Bei mir geht das nicht.
https://www.herber.de/bbs/user/98583.xlsm

Bild

Betrifft: Komisch...
von: Michael (migre)
Geschrieben am: 01.07.2015 18:03:49
Klexy,
... Aber ich reiche morgen meine Test Datei nach, bin nicht mehr am Rechner...
Sorry, inzwischen schönen Abend
Michael

Bild

Betrifft: @ Klexy: Schau mal...
von: Michael (migre)
Geschrieben am: 02.07.2015 08:59:59
Hallo und guten Morgen,
...diese meine Test-Datei an: https://www.herber.de/bbs/user/98592.xlsm
Klappt also mit dem AutoFilter inkl. Leerzeilen. Oder?
LG
Michael

Bild

Betrifft: AW: @ Klexy: Schau mal...
von: Klexy
Geschrieben am: 02.07.2015 11:06:01
Ich weiß jetzt auch, wann es funktioniert, nämlich wenn die Autofilterböppelchen noch nicht gesetzt sind. Wenn sie schon in der Kopfzeile vorhanden sind, funktioniert es nicht.
Aber warum?
Normalerweise (bei mir) sind die Filterböppelchen standardmäßig in der Kopfzeile jeder Liste, damit man sie jederzeit verwenden kann.

Bild

Betrifft: AW: @ Klexy: Schau mal...
von: Michael (migre)
Geschrieben am: 02.07.2015 11:19:24
Hi!
Ja, das scheint so. Wenn ich Code-mäßig mit dem AutoFilter hantiere, setze ich ihn meistens auf dem jeweiligen Blatt zurück bevor ich dann wieder einen neuen setze - damit wäre dann diese Fall auch abgedeckt.
Aber warum das mit den Leerzeilen bei bereits gesetztem AutoFilter nicht klappt? Evtl. wird bei gesetztem AutoFilter im Hintergrund der ursprüngliche Listenbereich mitgespeichert und nicht dynamisch angepasst (was ja logisch wäre, da Listen nunmal keine Leerzeilen haben [sollen]...).
Interessant ist noch
die Filterböppelchen standardmäßig in der Kopfzeile jeder Liste
Du meinst, Du musst prinzipiell den AutoFilter nicht selbst setzen? Bei Formatvorlage "Als Tabelle formatieren" kenne ich das, da wird gleich automatisch der AutoFilter gesetzt... aber bei normalen Tabellen? Da müsste doch bei Dir irgendeine Routine im Hintergrund laufen (in der .xlsb oder so)?
LG
Michael

Bild

Betrifft: AW: @ Klexy: Schau mal...
von: Klexy
Geschrieben am: 02.07.2015 14:27:20
Die Böppelchen setz ich händisch grundsätzlich in jede Tabelle, denn eine Tabelle ohne Filter kann man gleich auf ein Blatt Papier oder in PowerPoint schreiben.
Das mit dem Zurücksetzen des Autofilters ist (bei mir) problematisch, weil Tabellen zwar grundsätzlich bei A1 beginnen sollen, aber manchmal hab ich oberhalb der eigentlichen Tabelle Teilsummen-Zeilen, wodurch die Autofilter dann in die erste Blattzeile, aber nicht in die eigentliche Kopfzeile gesetzt werden würden.

Bild

Betrifft: Verstehe...
von: Michael (migre)
Geschrieben am: 02.07.2015 14:46:50
ohne Filter kann man gleich auf ein Blatt Papier oder in PowerPoint schreiben.
... ist klar ;-). Rücksetzen kann natürlich auch problematisch sein, da geb ich Dir auch Recht.
Abschließend ist wohl festzuhalten, dass beim Hantieren mit dem AutoFilter wiedermal besonders auf die konkreten Bedingungen in der jeweiligen Datei einzugehen ist.
Schönen Nachmittag
Michael

Bild

Betrifft: AW: Variable in Range einfügen
von: Rita
Geschrieben am: 02.07.2015 09:22:31
Guten morgen ihr zwei,
ich könnte euch abknutschen :-) es funktioniert beides!
Glücklicherweise habe ich sicher NIE Leerzellen. Viiiielen Dank euch beiden!
Eine letzte Frage hätte ich noch, dann wäre ich mit dem Programm fertig.
Es zielt auf eine ähnliche Frage hin.
Beispiel ist weiterhin die Tabelle von oben.
Ich würde gerne in Spalte D die Summe jeder einzelnen Zeile von A+B+C bilden und das ganze dann wieder genau für die länge meiner variablen Tabelle herunterleiten.
Die Länge kann ich aus A oder B oder C ermitteln, da hier immer was steht (D ist ja noch leer)
Da ich noch bei weitem nicht so fit in VBA bin habe ich es mit dem VBA Rekorder gemacht und statisch bis Zeile D10000 festgelegt, was natürlich quatsch ist, wenn meine Tabelle nur z.B. 300 Einträge hat.
Range("D1").Select ~f~
~f~ ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" 'Summe A1-C1 ~f~
~f~ Range("D1").Select ~f~
~f~ Selection.AutoFill Destination:=Range("D1:D1000"), Type:=xlFillDefault ~f~
~f~ 'Summe runterziehen bis auf z.B.I10000
Könntet ihr mir hierbei bitte nochmals helfen ? Vielen Dank !

Bild

Betrifft: AW: Variable in Range einfügen
von: Rita
Geschrieben am: 02.07.2015 09:25:24
'Summe runterziehen bis auf z.B.I10000 --> ich meinte natürlich:~f~
~f~'Summe runterziehen bis auf z.B.D10000

Bild

Betrifft: AW: Variable in Range einfügen
von: Michael (migre)
Geschrieben am: 02.07.2015 10:08:49
Hallo Rita!
Freut mich, dass ich helfen konnte. Bzgl. der Summenformel:

Sub SummeAbisC()
    Dim leZeile As Long
    leZeile = Worksheets("Tabelle1").Range("C1048576").End(xlUp).Row
    Range("D2").FormulaLocal = "=SUMME(A2:C2)"
    Range("D2").AutoFill Destination:=Range("D2:D" & leZeile)
         
End Sub
Im Code gehe ich jetzt davon aus, dass ab "A2", also ab der zweiten Zeile Deine Daten stehen - ggf. anpassen. Für die Bestimmung der letzten Zeile kannst Du hier genauso vorgehen wie o.a. (ich ermittle die letzte Zeile hier in Spalte C, aber das geht natürlich auch in A oder B... ). Beachte, dass hier bei Range kein Tabellenblatt vorangestellt ist (zB Worksheets("Tabelle1")... ) - d.h. der Code bezieht sich somit immer auf das aktive Tabellenblatt.
Alternativ kannst Du Dir in so einem Fall evtl. auch eine Variable sparen, und den Bereich so angeben:
Sub SummeAbisC()
    Range("D2").FormulaLocal = "=SUMME(A2:C2)"
    Range("D2").AutoFill Destination:=Range("D2:D" & Range("C2").End(xlDown).Row)
    
End Sub
Das setzt dann aber wieder voraus, dass Du keine Leerzellen in Spalte C hast, weil hier "von oben nach unten" die letzte Zeile in C geprüft wird.
Klappt?
LG
Michael

Bild

Betrifft: AW: Variable in Range einfügen
von: Rita
Geschrieben am: 02.07.2015 10:30:37
Danke, Danke, Danke !!!! :-)
Dank dir funktioniert das Programm nun super gut.
Damit sind meine beiden Fragen bestens beantwortet worden und das Thema kann geschlossen werden.
Ich wünsche dir/euch noch eine weiterhin sonnige Woche !

Bild

Betrifft: Gerne, Danke für die Rückmeldung! LG Michael owT
von: Michael (migre)
Geschrieben am: 02.07.2015 11:12:57
.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Variable in Range einfügen"