Alle Zeilen einer Tabelle in VBA dynamisch auswählen?

Hallo zusammen,

ich suche nach einer Zeile Code, mit der ich dynamisch die gesamte Tabelle auswählen kann.

Bislang habe ich verwendet:

Range("A1:U76").Select

Nun kann die Tabelle aber auch mehr oder weniger als 76 Zeilen haben (wenn sich die monatlichen Werte ändern).

Daher hatte ich an folgendes gedacht:

Range("A1:U" & ???).Select

(Zum inhaltlichen Verständnis: Danach werden Teilergebnisse hinzugefügt, und damit das funktioniert, ist die Tabelle "nicht intelligent/formatiert", also kein Listobject. Dafür habe ich allerdings schon funktionierenden Code.)

Die Frage ist jetzt, was ist ???.

Meine bisherigen Experimente waren leider nicht erfolgreich, deswegen frage ich nach einer Universallösung nach. Gerne neu aufschreiben.

Falls dies noch wichtig ist: Aufgrund der Datenlage sind nur die Spalten A-E vollständig mit Inhalten gefüllt, die weiteren Zellen weisen aufgrund der Datenlage Leerzellen ("null") auf.

Danke schon mal im Voraus.

3 Antworten

Von Experte Suboptimierer bestätigt

Krabat693

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Computer

16.07.2021, 10:23

Range("A1").CurrentRegion.Select

Edit:

Zuende lesen hilft, wenn Currentregion die restlichen Spalten wegen Leerzellen nicht erfasst könntest du auch einen kleinen Umweg machen:

Range(Cells(1,1),Cells(Range(A1).currentregion.rows.count,21)).select

Woher ich das weiß:Berufserfahrung – Administrator mit über 12 Jahren Berufserfahrung

3rr0r404

Fragesteller

16.07.2021, 11:38

Range("A1").CurrentRegion.Select hat tatsächlich schon mein Problem gelöst. Vielen Dank!

Krabat693

16.07.2021, 11:41

@3rr0r404

Mit .currentregion werden alle zusammenhängenden Zellen angesprochen. Der Umweg wäre erforderlich wenn eine leere Spalte im gewünschten Bereich liegt.

Der Umweg enthält ein .currentregion.rows.count welches die Anzahl der Zeilen des zusammenhängenden Bereiches ausgibt und über Cells(Zeile,Spalte) kannst du eine Zelle über numerische Werte ansprechen. Das ist auch praktisch wenn du mit einer Schleife zeilenweise die Tabelle durchgehen möchtest.

Suboptimierer

16.07.2021, 15:14

UsedRange könnte man noch einwerfen.

Elumania

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Computer

16.07.2021, 12:53

Range(Cells(zeileAnfang, spalteAnfang), Cells(zeileEnde, spalteEnde)).Select

Das sollte funktionieren. Für die Variablen zeile und spalte sind Zahlen einzusetzen.

LtLTSmash

Nutzer, der sehr aktiv auf gutefrage ist

im Thema Microsoft Excel

16.07.2021, 10:28

mit "Cells" kannst du die ganze Tabelle selektieren.

Cells.clearcontents würde z.B. in allen Zellen alle Werte löschen

Hallo,

ich habe ein kleines Problem mit dem Erstellen von mehreren PFD-Seiten aus unterschiedlichen Tabellenblätter. Je nach Erfüllung der Bedingung (If-Abfrage) wird eine PDF erstellt.

Sofern nur ein eine Tabelle angesprochen wird, gibt es keine Probleme mit der Erstellung. Dabei ist es egal, ob die Tabelle oder zwei Seiten hat. Die Formatierung/Range ist korrekt und das Makro läuft durch.


Sheets("DUZ").Select
Range("A1:Y52").Select
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\DUZ.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Aber soweit ich auf zwei oder mehr Tabellenblätter zugreife, gibt es Probleme:

Sheets(Array("Deckblatt", "Übersicht", "Tagebuch", "Ges", "Reisekosten", "DUZ")).Select
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\Tagebuch_RZ_DUZ.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Wie kann ich entsprechend die Range unterbringen, sodass es evtl. problemlos klappt?

'Sheets("Deckblatt").Select --> Hochformat (1 Seite)
'Range("A1:H43").Select
                    
'Sheets("Übersicht").Select --> Querformat (1 Seite)
'Range("A1:R35").Select
                    
'Sheets("Tagebuch").Select --> Hochformat (6 Seiten)
'Range("A1:V146").Select
                    
'Sheets("Ges").Select --> Hochformat (1 Seite)
'Range("A1:G43").Select
                    
'Sheets("DUZ").Select --> Querformat (2 Seiten)
'Range("A1:Y52").Select
                    
'Sheets("Reisekosten").Select --> Hochformat (1 Seite)
'Range("A1:N57").Select

Aktueller Fehlerhinweis: Anwendungs- oder objektdefinierter Fehler.

Ich hatte es auch mal wie folgt versucht, aber dann stimmt die Formatierung nicht (wenn es einzeln gespeichert wird, ist sie aber richtig). Außerdem wird dann beim Schreibschutz rumgemeckert (am Ende werden die Blätter wieder mit Schreibschutz versehen). Jedenfalls gibt es diese Probleme beim einzelnen nicht.

Sheets("DUZ").Select
Range("A1:Y52").Select
                    
Sheets("Reisekosten").Select
Range("A1:N57").Select

Sheets(Array("Reisekosten", "DUZ")).Select
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\Reisekosten_DUZ.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Hat jemand eine Idee?
Gruß

...zur Frage

Excel VBA: Schaltfläche die je nach 1., 2., 3. Klick verschiedene Funktionen ausführt?

Hallo zusammen,

ich suche eine Lösung um eine Schaltfläche mit folgender Funktion in meinen Code zu integrieren.

Klick auf schaltfläche --> Mache X
Klick auf Schaltfläche --> Mache Y

wie geht das am einfachsten?

_________________________________________________________

Anbei mein Code:

Sub NeuePosHinzu()

' NeuePosHinzu Makro

'1. Klick

Range("A1:F4").Select

Selection.Copy

Range("B8").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' Soll beim 2. Klick auf den Button ausgelöst werden

Range("A1:F4").Select

Selection.Copy

Range("B14").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' Soll beim 3. Klick auf den Button ausgelöst werden

Range("A1:F4").Select

Selection.Copy

Range("B20").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A14:G17").Select

Application.CutCopyMode = False

Selection.Copy

Range("A20:G23").Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("A20").Select

ActiveCell.FormulaR1C1 = "Pos. 3"

'Soll beim 4. Klick auf den Button ausgelöst werden

Range("A1:F4").Select

Selection.Copy

Range("B26").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A20:G23").Select

Application.CutCopyMode = False

Selection.Copy

Range("A26:G29").Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("A26").Select

ActiveCell.FormulaR1C1 = "Pos. 4"

Range("I21").Select

End Sub

...zur Frage

Excel: Dynamisch Bereich selber auswählen?

Liebe Community,

ich habe folgendes Macro in meiner Excel Tabelle. Es funktioniert auch wunderbar, bis auf die Tatsache, dass dort der Tabellenbereich aus der fremden Excel-Datei definiert ist.

Leider sind es nicht immer 239 Zeilen. Mal mehr und mal weniger. Dass es bei A7 anfängt, das stimmt schon. Wie müsste ich die Formel ändern, damit Excel sich automatisch anpasst?

Kann einer sagen, was ich nun genau ändern müsste in der betroffenen Zeile, damit Excel dynamisch sich den Bereich auswählt?

Vielen lieben Dank im Voraus!

Es handelt sich konkret um diese Zeile.

wbQuelle.Worksheets(1).Range("A7:I239").Copy
Sub Quelle()

Hier der gesamte Code

Dim sPfad As String
Dim wbQuelle As Workbook
Dim zelle As Range

sPfad = "C:\Users\Beispiel\Desktop\Irgendeine Datei\Daten\Daten.xlsx"

If Dir(sPfad) <> "" Then

Set wbQuelle = Workbooks.Open(sPfad)

wbQuelle.Worksheets(1).Range("A7:I239").Copy ThisWorkbook.Worksheets(3).Range("B2")

wbQuelle.Close savechanges:=False

End If

End Sub

...zur Frage

Excel - Zelleninhalte löschen, wenn in anderer Zelle "ein Wert" steht

Hallo liebes Forum,

ich habe ein kleines Problem bei einer kleinen Datenbank und hoffe das ihr mir weiterhelfen könnt.

Zum Problem: Ich habe in Zelle A1 eine Auswahlliste, bei der ich a,b oder c auswählen kann.

habe ich a ausgewählt, soll nichts passieren.
wähle ich jedoch b oder c aus, sollen im Zellbereich C4:F14 die Werte gelöscht werden

Dazu habe ich folgenden Code programmiert:

Private Sub Worksheet_TEST(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
        Select Case CInt(Range("A1"))
        Case b
            Range("C4:F14").Select
            Selection.ClearContents
        Case c
            Range("C4:F14").Select
            Selection.ClearContents
        End Select
    End If
End Sub

Ändere ich nun die Auswahl, passiert jedoch nichts, die Einträgen werden nicht gelöscht. Wo liegt der Fehler?

Danke für Eure Hilfe

Gruß diece

...zur Frage

Excel VBA -> Spalten- und Zeilenlängen und Schriftgröße bestimmen

Hallo,

kleine Frage: (wie) kann man im Code Spalten- und Zeilengröße und Schriftgröße bestimmen? Ich meine damit nicht per Hand oben im Menü selber ändern, sondern direkt im Code eine Zeile und Spalte ansprechen (z.B. Range("A1") = ...) und ihre Zeilen- und Spaltenlängen bestimmen. Das selbe auch für die Schriftgröße!

Kann mir da jemand helfen? Google konnte nichts Produktives liefern -
Code zum Farben einstellen dagegen habe ich erfolgreich gefunden

MfG

...zur Frage

Wie erstelle ich eine variable intelligente Tabelle?

Eine Bestandsauswertung wird in Excel(2010) ausgegeben und tägl. in meine zu analysierende Tabelle in einem Blatt (Abl.Schleifpuffer) abgelegt bzw. überschrieben. In diesem Blatt soll mittels VBA eine intelligente Tabelle erstellt werden.

Per Aufzeichnung klappt das ganz gut, nur gibt es da paar Haken:

sollte sich die Auswertung (Quelle) ändern aufgrund Programmanpassungen, verschieben sich auch die Zellen. So beginnt sie nicht von A11 sondern evtl. von B12
erstelle ich eine intelligente Tabelle, so gibt mir das Makro immer die Größe von der zuerst erstellten Tabelle "$A$11:$N$853" wieder, sodass bei der nächsten Auswertung die Zeilen ab 854 gar nicht mit einfließen. D.h. die Auswertung wird tägl. gemacht und die Länge ist definitiv variabel.

Wie bekomme ich Excel dazu das er automatisch erkennt "Ab Spalte A und Zeile 11 beginnt die Tabelle" und endet bei Spalte A und Zeile 999.

Derzeit ist die Aufzeichnung so verlaufen:

    Sub konvertiere_Abl_als_Tabelle()
'
' konvertiere_Abl_als_Tabelle Makro
'

'
    Sheets("Abl. Schleifpuffer").Select
    Range("A11").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$11:$N$853"), , xlYes).Name = _
        "Abl.Schleifpuffer"
    Range("Abl.Schleifpuffer[#All]").Select
End Sub

Für eure Unterstützung wäre ich sehr dankbar.

...zur Frage

Drehfeld in Excel programmieren

Würde gerne ein Drehfeld in Excel programmieren ich finde im Internet immer diese Codes.

Private Sub SpinButton1_SpinDown()
Range("A1") = Range("A1") - 0.1
End Sub

Private Sub SpinButton1_SpinUp()
Range("A1") = Range("A1") + 0.1
End Sub

Aber wie gebe den Code ein? Wenn ich auf Code anzeigen kommt dann kommt etwas mit SpinButton_Change und es macht wohl wenig Sinn das dort einzugeben. Und einzeln kann ich die Felder nicht aussuchen.

Kann mir jemand helfen? Gruß

...zur Frage

VBA Code: Zeilen mit Inhalt "leer" löschen?

Hallo an Alle,

kurz zur Erklärung: Ich füge Daten (immer 4 Zeilen) in eine "Datenbank" ein, allerdings, wenn nicht alles ausgefüllt wird, erscheint als Platzhalter "leer". Diese möchte ich gleich im Anschluss löschen, dass es keine weiteren Formeln durcheinander bringt bzw. weil er mir dann bei der nächsten Suche von einer leeren Zeile immer unterhalb der 4 Zeilen springt (wenn ich nicht mit "leer" fülle). :(

Ich habe jetzt schon mehreres probiert, aber entweder es löscht mir die Zeilen nicht oder es löscht auch Zeilen die eingefügt bleiben sollen... Bitte um fachmännische Hilfe. Bitte, DANKE!

Gerne umgehe ich auch "leer", wenn ich dann zumindest wirklich immer in der letzten freien Zeile bin...

Hier der momentan Code:

Range("N3:AE6").Select

Selection.Copy

Sheets("Datenbank").Select

Range("A520").Select

Cells(65000, 1).End(xlUp).Offset(1, 0).Select

Dim last As Integer

last = Cells(Rows.Count, 1).End(xlUp).Row + 1

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

If Cells(last).Value = "leer" Then

Rows(last).Delete Shift:=xlUp

End If

Sheets("BestandsKunde").Select

Range("H43").Select

End Sub

Lg, B

...zur Frage

VBA zeilen kopieren mit relativem Zellbezug

Hallo, ich habe in mein Excel Dokument einen Button eingebaut, den ich mit einem Makro verbinden möchte, der eine neue Zeile einfügt und eine darüberliegende vorhandene Zeile Kopiert bzw. nach unten zieht, um in dieser neue Werte eintragen zu können. Funktioniert soweit auch ....

ABER natürlich verschieben sich die Bezüge durch das einfügen neuer Zeilen und die Makros, welche sich auf Zeilen unter dem ersten Makro beziehen, funktionieren nicht mehr an gewünschter Stelle...

In Visual Basic habe ich versucht die "Range" Bezüge zu ändern, habe aber nur den ansatz für einzelne zellen gefunden z.b. "ActiveCell.Offset(1,0).Range("A1").Select"

Auf dem Bild soll jeweils das blaue kreuzchen eine neue Zeile einfügen, siehe Zeile Opex. Folgendes ist mein VB Quelltext bei dem sich immer alles verschiebt: Sub add_cost_netrent() ' ' dada Makro '

' Rows("9:9").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("D8:H8").Select Selection.Copy Range("D9").Select ActiveSheet.Paste ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 Range("U8:Y8").Select Application.CutCopyMode = False Selection.Copy Range("U9").Select ActiveSheet.Paste End Sub

...zur Frage

Sortieren in verschiedenen Tabellen?

Hallo zusammen,

ich habe momentan ein Problem, ich möchte, dass ich verschiedenen Tabellen auf einem Tabellenblatt habe.

Es soll jede Tabelle im Reiter "Kundengruppe" von A->Z (Aufsteigend) sortiert werden, ohne, dass das VBA direkt einen Zellenbezug herstellt, da die höhe und tiefe der Tabellen mal nach Anzahl der Eintragungen variieren kann.

Hier der Link zur Datei:

https://www.herber.de/bbs/user/147794.xlsm

Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.Worksheets("OP").AutoFilter.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("OP").AutoFilter.Sort.SortFields.Add2 Key:=Range( _

"Q16:Q18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("OP").AutoFilter.Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Das ist der aktuelle Code, der Bereich mit <b>Range( _"Q17:Q18")</b> soll im besten fall weg, da das der punkt ist, wo die höhe der Tabelle variieren kann..

Der Range(Selection, Selection.End(xlDown)).Select befehl, sucht ja in dem fall schon den richtigen anfang, und ab da soll dann der Filter gesetzt werden und gefiltert werden

Ich hoffe man versteht es einigermaßen...

Danke & LG

Chris

...zur Frage

Excel VBA Makro Code?

Guten Tag zusammen,

Undzwar geht es darum das ich ein Makro erstellt habe der sieht so aus :

Sub Makro1()

' Makro1 Makro

Range("D3").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("1M").Select

Range("C6").Select

ActiveSheet.Paste

Sheets("Tabelle1").Select

Range("J3").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("1M").Select

Range("C7").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Tabelle1").Select

Range("R3").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("1M").Select

Range("C13").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("D26").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Tabelle1").Select

Range("H3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

Das Problem ist jetzt das ich möchte das er in der Excel Tabelle jede Zeile mit diesem Makro ausfüllt sprich er soll bei Zeile 3 anfangen und bis 322 weiterführen ich habe 2 Mappen 1. "Tabelle1" und "1M"

Tabelle1 hatt die 322 Zeilen aber er springt nicht zur jeder Zeile und die Zelle "D3" , "J3", "R3", "H3" sollten dann auf die nächste Zeile Springen Sprich "D4", "J4" , "R3" , "H4" wenn er auch in der 4 Zeile ist

die restlichen Zellen sollen festbleiben zugehörig zu Mappe "1M"

ich hoffe es ist verständlich was ich meinte :'D

...zur Frage

Wie der Makro soll bestimmte Zellen ignorieren, wie mache ich das

Hallo an alle klugen Köpfe,

ich habe ein Makro erstellt, dass von einer Tabelle die Daten in die ne andere Tabele exportiert. was auch ganz gut klappt!

ich möchte aber das ausgeblendete und durhchgestrichene Zellen ignoriert werden oder besser gesagt nicht in die neue Tabelle mit-exportiert werden.

Hat da jemande eine schlaue Lösung?

Hier (ein Teil vom Makro) :

Range("C14").Select Selection.Copy Sheets("Tabelle2").Select

Range("N6:O6").Select
ActiveSheet.Paste    -----------> außer es ist ausgeblendet/durchges
Range("N7:O7").Select
Sheets("aktuell").Select
Range("G14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle2").Select
ActiveSheet.Paste
Sheets("aktuell").Select
Range("C15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle2").Se

Gruß Newb

...zur Frage

Excel VBA automatisch nächste freie Spalte nehmen?

hi hab mir mit Makros in VBA was zum übertragen von Daten in eine andere Arbeitsblatt zusammengebastelt. Dort soll es in eine Tabelle eingefügt werden die sich automatisch um eine Spalte nach rechts erweitern soll mit jedem Eintrag.

Sub inauftragplus()
   Range("D11:D22").Select
   Selection.Copy
   Sheets("Datenbank").Select
   ActiveWindow.SmallScroll Down:=-81
   Range("B9").Select
   ActiveSheet.Paste
   Sheets("Eingabe").Select
   Application.CutCopyMode = False
   Selection.ClearContents
End Sub

...zur Frage

VBA Ausgewählte Zeilen kopieren und in anderer Tabelle einfügen (mit Variablen)?

Hallo Zusammen,

Ich hab ein Problem. Ich würde gern durch das schreiben einer 1 hinter meiner Materialsammlung per Knopfdruck alle gewählten Zeilen in eine Zusammengestellte Tabelle einfügen.

Die Materialsammlung bei der ich auswähle ist in Tabelle zwei und wird dann per Knopfdruck abgefragt. Die markierten sollen dann in Tabelle 1 in eine Tabelle eingefügt werden.

Da ich aber mit Variablen Arbeite ist das schwieriger als gedacht

Hier mein code zum verdeutlichen

Private Sub CommandButton1_Click()

Dim strBereich As Range Dim x As Integer

For x = 3 To 60 If Worksheets(2).Cells(x, 10) = "1" Then

Worksheets(1).Range(Cells(x, 2), Cells(x, 9))=Worksheets(2).Range(Cells(x, 1), Cells(x, 8))

End If Next x

End Sub

funktioniert leider aber nicht.

Bitte im Hilfe

...zur Frage

Was möchtest Du wissen?

Deine Frage stellen