Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Größe benutzter Range ermitteln, Teil 2


Betrifft: Größe benutzter Range ermitteln, Teil 2 von: Hans-Jürgen Bartel
Geschrieben am: 05.02.2018 07:08:33

Hallo zusammen,

ich bin immer noch dabei, für einen Range zu ermitteln, inwieweit er durch den User gefüllt ist. (Siehe Frage von gestern).

Gestern war ich noch drauf aus, die Zeilennummern zu lokalisieren, um einen neuen Range zu "bauen". Mir hat die Lösung (vielen Dank auch an Sepp und Hajo) allerdings keine Ruhe gelassen, weil ich glaubte, dass ich mich vielleicht zu kompliziert ausgedrückt habe und ihr mit eurer Lösung mehr abgedeckt habt als erforderlich war.

Ich hatte nun eine andere Idee: Wir haben ein ähnliches Programm, das ein erfahrener Kollege geschrieben hatte (allerdings diese Woche in Urlaub). Ich wusste, dass an einer Stelle auch der gefüllte Bereich ermittelt werden musste, und er hat das so gelöst:

  • 
    Function BenutztenBereichErmitteln(aktBereich As Object) As Object
    
        Dim aktZelle As Object
        Dim aktLetzteZeile As Long
        
        For Each aktZelle In aktBereich
            If aktZelle.Value = "" Then
                aktLetzteZeile = aktZelle.Row - 1
                Exit For
            End If
        Next
    
        Set BenutztenBereichErmitteln = aktBereich.Resize(aktZelle.Row - aktBereich.Cells(1).Row)
    
    End Function


  • Ich habe das in mein Programm eingebaut, getestet und - funktioniert.

    Mein Problem ist allerdings: Bei obigem Ausschnitt war klar, dass der Range nur eine Spalte breit ist. Das passt aber nicht auf meine Anforderung. Ich brauche die Funktion möglichst variabel. Ich habe nun versucht, das anzupassen, indem ich zwei Schleifen baue, jeweils
    - aus der ersten Spalte die letzte benutzte Zeile und
    - aus der ersten Zeile die letzte benutzte Spalte
    rmittle um nachher zu "resizen". Ich habe die Schleifen so gebaut:

  •  For Each aktZelle In aktBereich.Columns(1)
            If aktZelle.Value = "" Then
                aktLetzteZeile = aktZelle.Row - 1
                Exit For
            End If
        Next
        For Each aktZelle In aktBereich.Rows(1)
            If aktZelle.Value = "" Then
                aktLetzteSpalte = aktZelle.Column - 1
                Exit For
            End If
        Next


  • Das klappt aber nicht, bereits die zweite Zeile (If aktZelle.Value = "" Then) bringt mir einen Typenkkonflikt und ich weß nicht, warum. Ich habe doch nur in der Schleife klar gestellt, dass ich nur die erste Spalte durchlaufen will. Wo ist der Denkfehler?

    Schon mal vielen Dank voraus für eure Hilfe, ihr seid wirklich klasse!

    Viele Grüße

    Hans-Jürgen

      

    Betrifft: meinst du nicht, du solltest..... von: Werner
    Geschrieben am: 05.02.2018 10:49:38

    Hallo Hans-Jürgen,

    ...erst einmal eine Rückmeldung auf Lösungsvorschläge in deinen anderen Beiträgen reagieren, bevor du einen neuen Beitrag eröffnest?

    Gruß Werner


      

    Betrifft: AW: meinst du nicht, du solltest..... von: Hans-Jürgen Bartel
    Geschrieben am: 05.02.2018 11:51:56

    Hallo Werner,

    ich habe die Antworten von gestern etwas schuldbewusst zur Kenntnis genommen. Schuldbewusst deshalb, weil es immer ein Spaghat ist, wie ausfühlich man die Frage stellt. Man will nicht schwafeln, aber auch nichts wichtiges weglassen. Und genau das habe ich scheinbar, weil ich nicht erklärt habe, wozu genau ich es brauchte. Dann hätte man mir wahrscheinlich direkt gesagt: "warum so kompliziert, gehr viel einfacher". Die Antworten (und der entsprechende Aufwand) waren auf meine unglücklich gestellte Frage perkekt zugeschnitten, aber durch meinen Fehler trotzdem noch nicht ganz die Lösung. Ich hatte mich in diesem Posting ja auch nochmal bedankt.

    Ich weiß, was ihr für tolle Arbeit macht und da ist es das Mindeste, dass der TE eine Rückmeldung gibt, insofern kann ich dein Posting nachvollziehen. In diesem Fall dachte ich aber, ein neuer Thread wäre sinnvoller, weil die Frage anders und viel konkreter ist. Übrigens hatte ich es auch mal aus einigen Signaturen entnommen, dass man keine Danke-Threads macht. Ich bedanke mich deshalb imner im voraus. Kann ich aber gern ändetn.

    Viele Grüße

    Hans-Jürgen


      

    Betrifft: AW: meinst du nicht, du solltest..... von: Werner
    Geschrieben am: 05.02.2018 13:13:18

    Hallo Hans-Jürgen,

    dann lies dir doch bitte mal unter Forum & Services die FAQ durch. Aber auch ohne den Hinweis darin gebietet das, meiner Meinung nach, die Höflichkeit.

    Bezüglich deines Hinweises, dass "man keine Danke-Threads" macht, beziehst du dich, meines Wissens nach, auf einen einzigen Teilnehmer hier im Forum. Dazu möchte ich aber lieber keine weiteren Erklärungen abgeben.

    Gruß Werner


      

    Betrifft: AW: meinst du nicht, du solltest..... von: Werner
    Geschrieben am: 05.02.2018 13:19:13

    Hallo Hans-Jürgen,

    und nun zu deiner eigentlichen Frage. Möglicherweise meinst du das hier:

    Option Explicit
    
    Public Sub Zeile_Spalte_Bereich()
    Dim loZeile As Long, loSpalte As Long, raBereich As Range
    
    With Worksheets("Tabelle1") 'Blattname anpassen
        loZeile = .Cells(.Rows.Count, 1).End(xlUp).Row
        loSpalte = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set raBereich = .Range(.Cells(1, 1), .Cells(loZeile, loSpalte))
        MsgBox "Die letzte Zeile ist:  " & loZeile _
        & vbLf & "Die letzte Spalte ist:  " & loSpalte _
        & vbLf & "Der Zellbereich ist:  " & raBereich.Address
    End With
    End Sub
    Gruß Werner


      

    Betrifft: AW: meinst du nicht, du solltest..... von: Hans-Jürgen
    Geschrieben am: 05.02.2018 13:47:34

    Hallo Werner,

    vielen Dank, aber das klappt leider noch nicht. Mal ganz konkret: Der Range, mit dem ich immer teste, hat den Bereich B7:B56 davon ist die Zelle B37 die letzte belegte.

    Die Abfrage End(xlUp).Row bzw. aktBereich.End(xlToLeft).Column hatte ich gestern im Rahmen meiner Recherche schon getestet und verworfen, denn ich bekam als Ergebnis 2 bzw. 1.

    Falls es erforderlich ist: Die Abfragen der ersten und letzten Spalte (habe ich auch aus dem Netz) erfolgen korrekt:
    aktBereich.Cells(1).Row ergibt 7
    aktBereich.Cells(1).Column ergibt 2
    aktBereich.Cells(aktBereich.Count).Row ergibt 56
    aktBereich.Cells(aktBereich.Count).Column ergibt 2.

    Die schon beschriebene Funktionerrechnet auch korrekt den belegten Bereich B2:B37. Aber leider funktioniert das nur einspaltigen Ranges, und das mit der Begrenzung auf die erste Spalte ist ja gescheitert.

    Vielen Dank nochmas (auch für die Etikette-Info, da werde ich mich natürlich sofort anpassen)

    Hans-Jürgen


      

    Betrifft: mach doch mal eine Mappe von: Werner
    Geschrieben am: 05.02.2018 13:56:30

    Hallo Hans-Jürgen,

    also ich weiß immer noch nicht, was du eigentlich willst. Mach doch mal eine Beispielmappe mit ein paar Daten in der du aufzeigst, was du ermittelt haben möchtest. Die Beispielmappe dann hier hochladen. Aber bitte als .xlsx, Dateien mit Makros kann ich im Moment nicht herunterladen.

    Gruß Werner


      

    Betrifft: AW: mach doch mal eine Mappe von: Hans-Jürgen
    Geschrieben am: 05.02.2018 14:43:45

    Hallo Werner,

    vielen Dank für das Angebot, durch die Antwort von Rudi habe ich die Lösung.

    Viele Grüße

    Hans-Jürgen


      

    Betrifft: Spatzen und Kanonen von: Rudi Maintaire
    Geschrieben am: 05.02.2018 13:59:01

    Hallo,
    die letzte beschriebene Zeile/ Spalte auf einem Blatt:

    Sub test()
      Dim x As Long, y As Long
      y = LastUsedColumnInRange(ActiveSheet)
      x = LastUsedRowInRange(ActiveSheet)
      If x = 0 And y = 0 Then
        MsgBox "Keine Zellen benutzt!"
      Else
        MsgBox Cells(x, y).Address
        'Application.Goto Cells(x, y), True
      End If
    End Sub
    
    Function LastUsedRowInRange( _
      wks As Worksheet, _
        Optional lngFirstRow&, _
        Optional lngLastRow&, _
        Optional lngFirstColumn&, _
        Optional lngLastColumn&) _
        As Long
      'Letzte Zeile mit Inhalt in einem Bereich
      '**************************************************
      'Parameter
      'wks: das Blatt
      'lngFirstRow&: die erste Zeile
      'lngLastRow&: die letzte Zeile
      'lngFirstColumn&: die erste Spalte, A=1, B=2 etc.
      'lngLastColumn&; die letzte Spalte
      '**************************************************
      
      Dim lngTmp&, blnFound As Boolean
      
      'sicherstellen, dass die Parameter gültig sind
      If lngFirstRow < 1 Or lngFirstRow > Rows.Count Then lngFirstRow = 1
      If lngLastRow < 1 Or lngLastRow > Rows.Count Then lngLastRow = Rows.Count
      If lngFirstColumn < 1 Or lngFirstColumn > Columns.Count Then lngFirstColumn = 1
      If lngLastColumn < 1 Or lngLastColumn > Columns.Count Then lngLastColumn = Columns.Count
      
      'drehen falls erster Wert > letzter Wert
      If lngFirstRow > lngLastRow Then
        lngTmp = lngLastRow
        lngLastRow = lngFirstRow
        lngFirstRow = lngTmp
      End If
      
      If lngFirstColumn > lngLastColumn Then
        lngTmp = lngLastColumn
        lngLastColumn = lngFirstColumn
        lngFirstColumn = lngTmp
      End If
      
      'jetzt geht's los
      With wks
        If Application.CountA(.Range(.Cells(lngLastRow, lngFirstColumn), .Cells(lngLastRow,  _
    lngLastColumn))) Then
          LastUsedRowInRange = lngLastRow: Exit Function
        End If
        If Application.CountA(.Range(.Cells(lngFirstRow, lngFirstColumn), .Cells(lngLastRow,  _
    lngLastColumn))) = 0 Then
          LastUsedRowInRange = 0: Exit Function
        End If
        
        lngTmp = (lngFirstRow + lngLastRow) / 2
        If lngLastRow > lngFirstRow + 1 Then
          If Application.CountA(.Range(.Cells(lngTmp, lngFirstColumn), .Cells(lngLastRow,  _
    lngLastColumn))) Then
            lngFirstRow = lngTmp
            blnFound = True
          End If
          If Not blnFound And _
            Application.CountA(.Range(.Cells(lngFirstRow, lngFirstColumn), .Cells(lngTmp,  _
    lngLastColumn))) Then
            lngLastRow = lngTmp
          End If
          LastUsedRowInRange wks, lngFirstRow, lngLastRow, lngFirstColumn, lngLastColumn
        End If
      End With
      LastUsedRowInRange = lngFirstRow
    End Function
    
    Function LastUsedColumnInRange( _
      wks As Worksheet, _
        Optional lngFirstRow&, _
        Optional lngLastRow&, _
        Optional lngFirstColumn&, _
        Optional lngLastColumn&) _
        As Long
      'Letzte Spalte mit Inhalt in einem Bereich
      '**************************************************
      'Parameter
      'wks: das Blatt
      'lngFirstRow&: die erste Zeile
      'lngLastRow&: die letzte Zeile
      'lngFirstColumn&: die erste Spalte, A=1, B=2 etc.
      'lngLastColumn&; die letzte Spalte
      '**************************************************
      
      Dim lngTmp&, blnFound As Boolean
      
      'sicherstellen, dass die Parameter gültig sind
      If lngFirstRow < 1 Or lngFirstRow > Rows.Count Then lngFirstRow = 1
      If lngLastRow < 1 Or lngLastRow > Rows.Count Then lngLastRow = Rows.Count
      If lngFirstColumn < 1 Or lngFirstColumn > Columns.Count Then lngFirstColumn = 1
      If lngLastColumn < 1 Or lngLastColumn > Columns.Count Then lngLastColumn = Columns.Count
      
      'drehen falls erster Wert > letzter Wert
      If lngFirstRow > lngLastRow Then
        lngTmp = lngLastRow
        lngLastRow = lngFirstRow
        lngFirstRow = lngTmp
      End If
      
      If lngFirstColumn > lngLastColumn Then
        lngTmp = lngLastColumn
        lngLastColumn = lngFirstColumn
        lngFirstColumn = lngTmp
      End If
      
      'jetzt geht's los
      With wks
        If Application.CountA(.Range(.Cells(lngFirstRow, lngLastColumn), .Cells(lngLastRow,  _
    lngLastColumn))) Then
          LastUsedColumnInRange = lngLastColumn: Exit Function
        End If
        If Application.CountA(.Range(.Cells(lngFirstRow, lngFirstColumn), .Cells(lngLastRow,  _
    lngLastColumn))) = 0 Then
          LastUsedColumnInRange = 0: Exit Function
        End If
        
        lngTmp = (lngFirstColumn + lngLastColumn) / 2
        If lngLastColumn > lngFirstColumn + 1 Then
          If Application.CountA(.Range(.Cells(lngFirstRow, lngTmp), .Cells(lngLastRow,  _
    lngLastColumn))) Then
            lngFirstColumn = lngTmp
            blnFound = True
          End If
          If Not blnFound And _
            Application.CountA(.Range(.Cells(lngFirstRow, lngFirstColumn), .Cells(lngLastRow,  _
    lngTmp))) Then
            lngLastColumn = lngTmp
          End If
          LastUsedColumnInRange wks, lngFirstRow, lngLastRow, lngFirstColumn, lngLastColumn
        End If
      End With
      LastUsedColumnInRange = lngFirstColumn
    End Function
    Gruß
    Rudi


      

    Betrifft: AW: Spatzen und Kanonen von: Hans-Jürgen
    Geschrieben am: 05.02.2018 14:46:37

    Hallo Rudi,

    vielen Dank! Ich habe erfolgreich "den Spatz aus der Kanone herausoperiert".

    Des Rätsels Lösung ist die CountA-Funktion, die ich an anderer Stelle im Programm benutze, aber völlig vergessen habe. Damit habe ich mein Problem gelöst.

    Hans-Jürgen