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

1 Antwort

Vom Beitragsersteller als hilfreich ausgezeichnet

Oubyi, UserMod Light

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

12.06.2022, 17:10

Ist das vielleicht immer der gesamte Bereich? Dann würde:

wbQuelle.Wordsheets(1).UsedRange.Copy

reichen. Falls nur ÜBER Zeile 7 etwas steht, könntest Du das auch nachträglich löschen.
Sonst müsstest Du erst die letzte Zeile herausfinden. Z.B.:

Dim loletzte As Long
loletzte = Cells(Rows.Count, 1).End(xlUp).Row

Hilft Dir das schon weiter?

Woher ich das weiß:Berufserfahrung – IT-Administrator (i.R.)

Pambott

Beitragsersteller

12.06.2022, 17:14

@Oubyi, UserMod Light

Hallo. Danke!

Es fängt immer bei A7 an und geht bis zur Spalte I7. Das ist fest! Aber die Zeilen darunter sind je nach Datei unterschiedlich lang. Mal sind es 237 oder 450 Einträge.

Mit dieser Zeile mache ich den Bereich ja fest, welcher kopiert werden soll. Nun möchte ich jedoch nicht immer die Zahl am Ende manuell verändern, bevor ich das Makro ausführe.

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

Oubyi, UserMod Light

12.06.2022, 17:17

@Pambott

Hab ich Dir doch beschrieben.

Dim loletzte As Long
loletzte = Cells(Rows.Count, 1).End(xlUp).Row
Range("A7:I" & loletzte).Copy

Klappt das nicht?

Pambott

Beitragsersteller

12.06.2022, 17:22

@Oubyi, UserMod Light

Hey, ich hab das noch nicht probiert, weil dein Code nicht zu meinem Code passt. Was meinst du mit Dim loletzte?

Ich müsste folgendes ändern.

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

Oubyi, UserMod Light

12.06.2022, 17:28

@Pambott

Oh, ich dachte, Du kennst Dich ein bisschen aus in VBA.

Mit Dim ...wird ein Variable dimensioniert (festgelegt).
Das kommt in die erste Zeile.
In die zweite Zeile dann das

loletzte= ....

Und dann ersetzt Du DEIN

Range("A7:I239").Copy

durch mein Konstrukt. Der Bezug davor bleibt so.

P.S.: Bin jetzt erstmal off. Schaue in ein paar Stunden wieder vorbeil

Pambott

Beitragsersteller

12.06.2022, 18:29

@Oubyi, UserMod Light

Hey, hat geklappt. Super vielen lieben Dank dir! Ja, ich bin noch kein Profi, brauche ab und an einfach eine etwas genauere bzw. vereinfachte Erklärung. :P

Oubyi, UserMod Light

12.06.2022, 20:33

@Pambott

Freut mich, dass Du es hinbekommen hast. 😊

Ähnliche Beiträge

Excel Macro: Formel dynamisch machen, um Zellenbereich auszuwä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?

Vielen lieben Dank im Voraus!

Es handelt sich um diese Zeile wbQuelle.Worksheets(1).Range("A7:I239").Copy

Sub Quelle()

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

...zum Beitrag

Excel: Variablen in Makros einfügen und in Datenbank pflegen/ändern?

Hallo liebe Community,

ich habe diverse Makros in meiner Excel Datei und ich würde gerne das ganze etwas "smarter" gestalten und eine Art Datenbank mit VBA erstellen, um viele Sachen in der Arbeitsmappe dadurch ändern/pflegen zu können. Externe Leute sollen und müssen dann nicht mehr in die VBA Oberfläche und mühsam das Makro suchen, um dort Pfade oder Zeilen zu ändern, falls sich was ändert.

Meine Frage:

Ist es möglich in ein Makro bei VBA eine Art Variable einzubauen bzw. Pfad, wo Excel z.B. in Tabelle1 in Spalte A1 den Wert/Namen findet?

Beispiel Makro:

Statt Daten123.xlsx soll dann ein Bezug hin, wo Excel in Tabelle1 in A1 den Namen Daten123.xlsx findet. Ist sowas möglich?

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

sPfad = "C:\Users\P\Desktop\Dateiordner\Unterordner\Daten123.xlsx"

If Dir(sPfad) <> "" Then

Set wbQuelle = Workbooks.Open(sPfad)

Call Test

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

wbQuelle.Worksheets(1).Range("A7:I" & loletzte).Copy ThisWorkbook.Worksheets(3).Range("B2")

wbQuelle.Close savechanges:=False

End If

...zum Beitrag

Excel VBA Zellen in ein anderes Tabellenblatt Kopieren wenn Bedingung erfüllt?

Hallo,

ich habe ein problem und habe keine Ahnung von VBA.

Ich habe eine Tabelle in "A" steht der Name in "P" der Kurs und in "Y" ist die Bedingung "ja" oder "nein".

Das war das einzige das ich gefunden habe was auch funktionierte. Allerdings Kopiert es die komplette Zeile.

Sub BedingteKopieZeilen()

Dim Zeile As Long

Dim ZeileMax As Long

Dim n As Long

With Tabelle2

ZeileMax = .UsedRange.Rows.Count

n = 7

For Zeile = 3 To ZeileMax

If .Cells(Zeile, 25).Value = "Kurtaxe" Then

.Rows(Zeile).Copy Destination:=Tabelle10.Rows(n)

n = n + 1

End If

Next Zeile

End With

End Sub

Ich bedanke mich für eure Hilfe

Bernd

...zum Beitrag

Excel Makro, PDF versenden, Anhang klappt nicht?

Hallo, ich würde gerne eine Rechnung mit einem Makro erstellen. Soweit klappt das auch und die Mail öffnet sich auch, aber diese wird nicht als Anhang hinzugefügt. Ich komme leider nicht drauf. Ich denke, es liegt daran, dass er die Datei nicht findet, aber ich wüsste keinen Lösungsansatz. Hier der Code:

Dim Result As VbMsgBoxResult

Sub RechnungsnrUndDrucken()

Sheets("Rechnung_Video").Select

[D15] = [D15] + 1 'Rechnungsnummer 1 hochzählen

Dim sCC As String

Dim sName As String

Dim sText As String

Dim sObject As String

Dim sCompany As String

Dim sRefNr As String

Dim sTempFileName As String

If (CStr(ThisWorkbook.ActiveSheet.Name) = "Rechnung") Then

Result = MsgBox("Rechnung fertig?", vbApplicationModal Or vbQuestion Or vbYesNo)

If Result = vbNo Then Exit Sub

End If

Const DateiPfad = "C:\Temp\"

Dim DateiName As String

DateiName = DateiPfad & Range("C15") & Range("D15") & ".pdf"

Range("A1:F54").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

DateiName, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=False

Dim Nachricht As Object, OutApp As Object

Dim AWS As String

Set OutApp = CreateObject("Outlook.Application")

AWS = strFileName

'InitializeOutlook = True

Set Nachricht = OutApp.CreateItem(0)

With Nachricht

.Subject = "" & Range("A1") & "_" & Range("C15") & Range("D15")

.GetInspector.display

'.Attachments.Add ("C:\Temp\" & Range("C15") & Range("D15") & ".pdf")

End With

Set OutApp = Nothing

Set Nachricht = Nothing

End Sub

...zum Beitrag

Excel VBA jede n-te Zeile finden und bei der Ausgabe die Steps ignorieren?

Moin zusammen, ich baue aktuell in VBA eine Excel Liste bzw. versuche eine auszuwerten. Ziel, jede 21ste Zeile soll ausgelesen und zusammengefasst ausgegeben werden. Leider gibt er die Werte genau in den Steps aus in denen er Sie liest und ich komme mit meinem Probieren nicht weiter. Hat jemand eine Lösung?

Hier der Code:

Sub AlternativeZeilenAuswaehlen()

Dim bereich1 As Range

Dim bereich2 As Range

Dim x As Integer

Dim y As Integer

Dim ZeilenNr As Integer

'Den Bereich auswählen

Set bereich1 = Range("'Test Yvonne Excel (2)'!E1:E30000")

Set bereich2 = Range("Tabelle1!B5:B30000")

'Zeilen zählen

ZeilenNr = bereich1.Rows.Count

ZeilenNr = bereich2.Rows.Count

'Jede zweite Zelle im Bereich durchlaufen

For x = 3 To ZeilenNr Step 21

'den Wert aus Spalte B in Spalte C eintragen

bereich2.Cells(x, 1).Offset(0, 0) = bereich1.Cells(x, 1)

End Sub

...zum Beitrag

ExCel VBA Werte in anderes Tabellenblatt?

Hallo, kurze Frage zu VBA, falls sich da jemand findet der das gut kann ^^

Wir müssen zur Hausübung folgendes Szenario "erschaffen":
Wir haben die Tabelle "Buchungsmaske", die in Spalte B freie Felder zur Eingabe von Werten hat (Beispiel: Datum - Konto-Nummer - Kontobezeichnung...). Diese sollen dann durch die Schaltfläche "speichern" in das bereits vorhandene Tabellenblatt "Buchungen" gespeichert werden, und zwar in die nächste freie ZEILE.

Ich habe schon danach gesucht aber finde kaum anwendbares für diesen Sachverhalt in Foren, bzw. suche ich möglicherweise auch falsch.

Mein Code derzeit:
Dim Copy As Range, Paste As Range

Dim aWerte()

Set Copy = Sheets("Buchungsmaske").Range("B4:B20")

Set Paste = Sheets("Buchungen").Range("A1:U20")

If Sheets("Buchungsmaske").CheckBox1.Value = True Then

aWerte() = Copy

Paste = aWerte()

End If

Laufzeitfehler der immer auftaucht ist "438"

Danke für die Hilfe im Vorraus!

...zum Beitrag

Excel VBA Einträge als Liste untereinander kopieren?

Hay zusammen,

meine Frage ist wie kann ich die Werte Links in der Tabelle rechts untereinander einfügen? Es muss eine Abfrage erfolgen ob die Zeile leer ist und wenn nicht, dann darunter einfügen.

Habe da ein Makro gefunden aber vielleicht kann mir jemand sagen wie der funktioniert. Kenne mich damit nicht aus. Habe die Werte geändert (Zellenangaben) und trotzdem fügt er die Daten untereinander ein.

Sub Makro2()
'
' Makro2 Makro
'
' Tastenkombination: Strg+e
'
Dim Zelle As Range
Dim Bereich As Range
Set Bereich = Range("J3:N3")
For Each Zelle In Bereich
    If IsEmpty(Zelle) Then Exit For
Next Zelle
With ActiveSheet
    .Range("A3:E3").Copy
    Zelle.PasteSpecial Paste:=xlPasteAll, Transpose:=True
    .Range("A3:E3").ClearContents
End With
Application.CutCopyMode = False
End Sub

Verstehe das System nicht ganz.

Kurz: Durch Klick auf Buchen sollen die Werte rechts eingefügt werde. Untereinander bei jeder weiteren Buchung.

...zum Beitrag

VBA Input-Eingabe in Zelle eintragen?

Guten Abend,

ich möchte gerne den Inhalt aus einer Inputbox in Zelle D28 schreiben, nur leider funktioniert es nicht.

Folgendes Makro: (sicherlich nicht schön geschrieben aber es bis auf die Zelle D28)

Sub erstellen()
   Dim NeuerTabellenName As String
   Dim TabellenName As Boolean
   Dim myInput As String
   Dim WsTabelle As Worksheet
   
   myInput = InputBox("Bitte geben Sie den Namen des neuen Monates an", "Monat anlegen")
   
   For Each WsTabelle In Worksheets
   If WsTabelle.Name = myInput Then
   TabellenName = True
   Exit For
   End If
   Next WsTabelle
   If TabellenName Then
   MsgBox ("Der Monat existiert bereits. Der Vorgang wurde abgebrochen. Bitte ueberpruefen Sie Ihre Eingabe.")
   Worksheets("Admin").Select
   Else
   NeuerTabellenName = myInput
   Worksheets("Muster").Copy After:=Sheets(Sheets.Count)
   Sheets(Sheets.Count).Name = NeuerTabellenName
   Worksheets("Admin").Range(D28).Value = myInput
   Worksheets("Admin").Select
   End If
End Sub

Der Fehler muss ja hier liegen:

Worksheets("Admin").Range(D28).Value = myInput

Über eine Lösung wäre ich sehr dankbar.

Gruß

...zum Beitrag

VBA CODE Zellen nach bestimmten Textinhalt suchen und Zeile Färben?

Hallo, kann mir bitte jemanden mit dem Code behilflich sein?

Dieser Code sucht die Zellen in Spalte"A" nach bestimmten Textinhalt ab und bei Treffer löscht er die zughörige gesamte Zeile. Meine Frage: kann man diesen Code so ändern, dass anstatt die gefundene Zeile zu löschen nur mit einer Farbe z.B. Orange (44) färbt? Vielen Dank im Voraus.

Sub zeilenloeschen()
  Dim i As Long
  Dim letzteZeile As Long
  Dim suchBereich As Range
  Dim gefunden As Range
  Dim ersterTreffer As String
  Dim suchWert As String
  suchWert = "1" 'hier kommt dein Suchwert rein
  
  With ThisWorkbook.Sheets("Sheet4")
    letzteZeile = .Range("A" & Rows.Count).End(xlUp).Row
    Set suchBereich = .Range("A1:A" & letzteZeile)
    Set gefunden = suchBereich.Find(What:=suchWert, LookAt:=xlPart)
    If Not gefunden Is Nothing Then
      ersterTreffer = gefunden.Address
      Do
        gefunden.Activate
        ActiveCell.EntireRow.Delete shift:=xlUp
        Set gefunden = suchBereich.Find(What:=suchWert, LookAt:=xlPart)
      Loop While Not gefunden Is Nothing
    End If
  End With
End Sub

...zum Beitrag

Doppelte Werte suchen mit zusätzliche Bedingung dann eins löschen?

Hallo zusammen, ich hoffe dass jemand helfen kann weil ich weiß nicht weiter, danke.

Ich habe folgenden Code zum Suchen der Doppeltwerten in Spalte B und dann Löschung der Zeile:

Sub DoppelteZeilenLoeschen()

Dim letzteZeile As Long

Dim Zeile As Long

letzteZeile = Range("B" & Rows.Count).End(xlUp).Row

For Zeile = letzteZeile To 1 Step -1

If WorksheetFunction.CountIf(Range("B1:B" & Zeile), Range("B" & Zeile)) > 1 Then

Rows(Zeile).EntireRow.Delete

End If

End Sub

Jetzt meine Frage:

Was ich kann ich ergänzen, dass eine zweite Bedingung hier abgefragt wird vor der Löschung, also wenn Spalte "B" doppelwert hat dann Spalte "J" vergleichen, wenn Ergebnis der Spalte "J" gleich ist dann Zeile löschen, ansonsten weiter zählen.

Ein Bsp.

Spalte B Spalte J

ABCD 1234
ABCD 1234
ABCD 4567

Hier sollten nur Zeile2 gelöscht werden.

Vielen Dank im Voraus

...zum Beitrag

Excel VBA Selection begrenzen?

Hallo zusammen.

Ich komme leider beim meinem Problem nicht alleine weiter.

In meiner Tabelle werden in Spalte 2 und Zeile 4 Zeichen angezeigt, sobald ein Bereich selektiert wird. Die Zeichen sollen sich allerdings nur auf die Range J7:OJ57 beschränken, auch wenn die Selektion über die Range hinausgeht.

Denn jetzt, wenn ich z.B. eine Spalte markiere, schreibt der Code mir alle Zeichen in jede Zeile der Spalte, was fatal ist.

Kann ein Profi kurz helfen? :)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J6:OJ57")) Is Nothing Then
Dim cell As Range
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.EnableEvents = False
   Range("B6:B57").ClearContents
   Range("J4:OJ4").ClearContents
For Each cell In selection
  With cell
   Range(cell(.Row, 2), Cells(.Row, 2)) = ">>"
   Range(cell(4, .Column), Cells(4, .Column)) = "V"
  End With
Next
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   Application.Calculation = xlCalculationAutomatic
End If
End Sub

...zum Beitrag

Excel VBA: Bild einfügen, in Variable speichern, Größe ändern, ausrichten?

Ich habe jetzt unzähle Varianten durch. Ich baue ein Bestellsystem für zwei verschieden Shops. Heißt, bei jedem eingetragenen Artikel in die Bestellliste soll am Ende der Listen-Zeile das jeweilige Logo eingefügt werden, kleiner als das Originalbild, kleiner als die Zeile selbst, für die Übersichtlichkeit in der Liste, mittig ausgerichtet.

Ich habe von Pictures.Insert zu Shapes.AddPicture gewechselt, da ich gelesen habe, dass dies die korrektere Form sei. Jetzt bekomme ich eine Fehlermeldung bei der Festlegung eines Rahmens.
Kann mir jemand meine Fehler aufzeigen? Folgende Fragen stellen sich mir:
- Welchen Variablen-Typ brauche ich für ein eingefügtes Bild?
- Wie kann ich diese Variable dann verwenden, um Größe, Position und Rahmen festzulegen?

Danke sehr!!!!!!

Sub LogoEinfügen(Shop As String, Zeile As Integer, Spalte As Integer)

Dim Breite As Integer
Dim Höhe As Integer
Dim strDatei As String
Dim Logo As Object
Dim ShopFarbe As Long, Rot As Long, Blau As Long

' Farben für die zwei Shops festlegen
Rot = 26316
Blau = 13395456

' Dateinamen je nach Shop auswählen und Shop-Farbe einstellen
If Shop = "ZL" Then
  strDatei = "D:\logo1.jpg"
  ShopFarbe = Rot
End If

If Shop = "AQ" Then
  strDatei = "D:\logo2.jpg"
  ShopFarbe = Blau
End If

' Bild einfügen

' Ort festlegen
Dim rg As Range
Set rg = ActiveSheet.Cells(Zeile, Spalte)

' Und einfügen
Set Logo = ActiveSheet.Shapes.AddPicture(strDatei, msoTrue, msoTrue, rg.Left, rg.Top, -1, -1)
Set rg = Nothing

With Logo
      .LockAspectRatio = msoFalse              ' Verzerrung egal
      .Height = Rows(Zeile).RowHeight - 4      ' kleiner als Zeile
      .Width = Columns(Spalte).Width - 4       ' schmaler als Spalte
      .Top = Cells(Zeile, Spalte).Top + (Cells(Zeile, Spalte).Height - Logo.Height) / 2         ' mittig
      .Left = Cells(Zeile, Spalte).Left + (Cells(Zeile, Spalte).Width - Logo.Width) / 2          ' mittig
  End With
  
  ' Rahmen ums Bild, in der Farbe des Shops
  With Logo.ShapeRange.Line
    .Visible = msoTrue
    .ForeColor.RGB = ShopFarbe
    .Weight = 1.5
    .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
End With

Set Logo = Nothing

' Zeilenhöhe anpassen
' Rows(Zeile).RowHeight = Logo.Height
 
End Sub

...zum Beitrag

Fehlermeldung in VBA: Typen unverträglich?

Hallo,

ich kriege eine Fehlermeldung in VBA, finde allerdings keine Lösung, dies zu beheben. Könnt ihr mir helfen?

Sub Berichte()
    Dim Liste As Workbook
    Dim Bericht As Workbook
    Dim Dati As Variant
    
    Set Bericht = ActiveWorkbook
    Dati = Application.GetOpenFilename("Excel-Dateien(*.xl*),*.xl*")
    Set Liste = Workbooks.Open(Filename:=Dati)
    ActiveWorkbook.Sheets("Tabelle1").Columns("A:E").Copy
    
    Windows(Bericht).Activate
    Columns("A:E").Select
    ActiveSheet.Paste

End Sub

...zum Beitrag

VBA Zellen verbinden mit schleife?

Hallo,

ich möchte mit Visual Basic in der Spalte A jeweils die 4 aufeinander folgenden Zellen miteinander verbinden. Also A4 - A7, A8 - A11 usw. In der Zeile mit range spuckt er einen Fehler und ich komme nicht drauf warum. Wäre super, wenn jemand mir helfen könnte 😁

Quellcode:

Option Explicit
Sub Merge()
Dim Counter1 As Integer
Dim Counter2 As Integer
Counter1 = 3
Counter2 = 7
Worksheets(„Tabelle1“).Activate
Do While Counter1 <= 1000
Range(Cells(Counter1,1), Cells(Counter2,1)).Select = True
Selection.Merge
Counter1 = Counter1 + 4
Counter2 = Counter2 + 4
Loop
End Sub

...zum Beitrag

Was möchtest Du wissen?

Deinen Beitrag erstellen