Microsoft Excel

Herbers Excel/VBA-Archiv

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

Bereich selektieren

Betrifft: Bereich selektieren von: Dirk N.
Geschrieben am: 21.08.2008 17:09:30

Hallo mal wieder,

wie kann ich mittels VBA den kompletten Bereich selektieren (analog zur Markierung mittels "Rahmen") , in dem sich Einträge befinden ?

Das Problem dabei sind leere Zeilen/Spalten, die davor od. mittendrin vorkommen können.
Der farbige Bereich soll also selektiert werden (zur späteren Weiterverarbeitung):



Mit UsedRange, CurrentRegion etc. komme ich irgendwie nicht weiter...

MfG Dirk

  

Betrifft: AW: Bereich selektieren von: Jürgen
Geschrieben am: 21.08.2008 17:44:30

Hallo Dirk,

was läuft falsch mit UsedRange? Damit solltest Du genau den gewünschten Bereich erhalten.

Gruß, Jürgen


  

Betrifft: UsedRange.Select - geht doch ... von: Matthias L
Geschrieben am: 21.08.2008 17:45:44

Hallo



Gruß Matthias


  

Betrifft: AW: Bereich selektieren von: Tino
Geschrieben am: 21.08.2008 17:59:07

Hallo,
verstehe ich dich richtig, es stehen in den Zellen außerhalb der Farblichen Markierung auch Werte.

Spontan fällt mir nur diese Lösung ein, vielleicht geht’s auch einfacher.

Sub test()
Dim lngRow As Long, lngColumn As Long
Dim Letzte As Range, Erste As Range
Dim A As Long
'Hat Zelle keine Farbe wird der Code umgangen!
If ActiveCell.Interior.ColorIndex <> xlNone Then
'Letzte Zelle Suchen**************************************
For A = 1 To Rows.Count

 If Cells(ActiveCell.Row + A, ActiveCell.Column).Interior.ColorIndex = _
    ActiveCell.Interior.ColorIndex Then
   lngRow = ActiveCell.Row + A
 Else
  Exit For
 End If
Next A

For A = 0 To Columns.Count

 If Cells(ActiveCell.Row, ActiveCell.Column + A).Interior.ColorIndex = _
    ActiveCell.Interior.ColorIndex Then
   lngColumn = ActiveCell.Column + A
 Else
  Exit For
 End If
Next A

Set Letzte = Cells(lngRow, lngColumn)

'Erste Zelle Suchen******************************************
For A = 0 To Rows.Count

 If Cells(ActiveCell.Row - A, ActiveCell.Column).Interior.ColorIndex = _
    ActiveCell.Interior.ColorIndex Then
   lngRow = ActiveCell.Row - A
 Else
  Exit For
 End If
Next A

For A = 0 To Columns.Count

 If Cells(ActiveCell.Row, ActiveCell.Column - A).Interior.ColorIndex = _
    ActiveCell.Interior.ColorIndex Then
   lngColumn = ActiveCell.Column - A
 Else
  Exit For
 End If
Next A
Set Erste = Cells(lngRow, lngColumn)

 Range(Erste, Letzte).Select
End If

End Sub



Gruß Tino


  

Betrifft: AW: Bereich selektieren von: Tino
Geschrieben am: 21.08.2008 18:31:53

Hallo,
in dieser Zeile habe ich was übersehen.

For A = 1 To Rows.Count

sollte

For A = 0 To Rows.Count

sein!

Gruß Tino


  

Betrifft: AW: Bereich selektieren von: Erich G.
Geschrieben am: 21.08.2008 18:47:13

Hi Dirk,
der UsedRange ist manchmal größer als aktuell nötig - z. B. wg. vorangegangener Löschungen.

Die folgenden Funktionen bestimmen den Bereich von der ersten und der letzten Zelle mit einem Wert > "".
Formatierungen (z. B. Farben) werden - im Gegensatz zu UsedRange - nicht berücksichtigt.

Bei dieser Tabelle ergeben sich:
NichtLeererBereich: C6:E8
UsedRange: B5:F9 (mindestens)

 BCDEF
5     
6 1   
7     
8   xxx 
9     

Und hier der Code:

Option Explicit

Sub test()
   MsgBox NichtLeererBereich().Address
   MsgBox "UsedRange: " & ActiveSheet.UsedRange.Address
End Sub

Function NichtLeererBereich(Optional ByVal rngB As Range) As Range
   If rngB Is Nothing Then Set rngB = Cells
   Set NichtLeererBereich = Range( _
      Cells(ErsteZeileInBereich(rngB), ErsteSpalteInBereich(rngB)), _
      Cells(LetzteZeileInBereich(rngB), LetzteSpalteInBereich(rngB)))
End Function

Function ErsteZeileInBereich(rngB As Range) As Long
    Dim rng As Range
 
    Set rng = rngB.Find("*", rngB.Cells(rngB.Row + rngB.Rows.Count - 1, _
      rngB.Column + rngB.Columns.Count - 1), xlValues, , xlByRows, xlNext)
    If rng Is Nothing Then
       ErsteZeileInBereich = rngB.Cells(1, 1).Row
    Else
       ErsteZeileInBereich = rng.Row
    End If
End Function

Function ErsteSpalteInBereich(rngB As Range) As Long
    Dim rng As Range
 
    Set rng = rngB.Find("*", rngB.Cells(rngB.Row + rngB.Rows.Count - 1, _
      rngB.Column + rngB.Columns.Count - 1), xlValues, , xlByRows, xlNext)
    If rng Is Nothing Then
       ErsteSpalteInBereich = rngB.Cells(1, 1).Column
    Else
       ErsteSpalteInBereich = rng.Column
    End If
End Function

Function LetzteZeileInBereich(rngB As Range) As Long
    Dim rng As Range
 
    Set rng = rngB.Find("*", rngB.Cells(1, 1), xlValues, , xlByRows, xlPrevious)
    If rng Is Nothing Then
       LetzteZeileInBereich = rngB.Cells(1, 1).Row
    Else
       LetzteZeileInBereich = rng.Row
    End If
End Function

Function LetzteSpalteInBereich(rngB As Range) As Long
    Dim rng As Range
 
    Set rng = rngB.Find("*", rngB.Cells(1, 1), xlValues, , xlByColumns, xlPrevious)
    If rng Is Nothing Then
       LetzteSpalteInBereich = rngB.Cells(1, 1).Column
    Else
       LetzteSpalteInBereich = rng.Column
    End If
End Function

Wenn alle Zellen erfasst werden sollen, in denen Formeln stehen,
kann man xlValues durch xlFormulas ersetzen.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Bereich selektieren von: Dirk N.
Geschrieben am: 21.08.2008 21:25:25

Hallo euch allen,

vielen Dank für eure Antworten u. die viele Mühe...
Außerdem muss ich mich wohl auch für meine unpräziese Beschreibung des Gesamtproblems entschulden - ich war mir dessen nicht bewusst.
Habe auch noch im Archiv das gefunden (leider erst jetzt):
https://www.herber.de/forum/archiv/972to976/t975451.htm

@Tino
Außerhalb dieses Bereiches sollte eigentlich nix mehr stehen - zumindest ist auch hix sichtbar.
Allerdings mache ich mit deinem Code wohl etwas falsch - ich habe ihn in das Blatt eingefügt, aber es passiert nichts... Es wird nichts selektiert, nur die aktive Zelle bleibt aktiv...
Was mache ich verkehrt ?

@Erich
Jetzt kriege ich Angst... ;-) Woher wusstest du von den "vorangegangenen Löschungen" ?
Entweder hast du es in deiner Glaskugel gelesen od. du erinnertest dich an meine vorangegangene Frage ( https://www.herber.de/forum/archiv/1000to1004/t1003340.htm )... ;-)

Kompliment in jedem Falle an dein Gedächtnis u. deine Fähigkeit, sofort den Überblick zu gewinnen (das ist aber echt fast schon gespenstisch - zumindest aus meiner Sicht) !

Tatsächlich zeigen die MsgBoxen folgende Abweichungen:
"Nicht leerer Bereich: A2:Q7" - genau diese Werte brauche ich
"UsedRange: A1:IU7" - für mich unverständlich...

Zwar weiß ich noch nicht, wie ich den Wert, den die 1, MsgBox liefert, selektieren u. weiterverarbeiten kann - aber das kriege ich mit viel Basteln bestimmt hin - will ja nicht ewig der DAU bleiben... ;.)

Also vielen Dank euch allen - ihr habt mich ein riesiges Stück weitergebracht !

MfG Dirk

P.S.: "offen" nur deshalb, da ich mit Tino's Variante nicht klarkomme u. ich meinen Fehler nicht finden kann.


  

Betrifft: AW: Bereich selektieren von: Tino
Geschrieben am: 21.08.2008 21:33:44

Hallo,
habe deine frage wohl falsch verstanden!
Mein Code selektiert den Bereich der Zellen, der einen farbigen Hintergrund hat.
Hätte deine Frage besser lesen müssen. sorry

Gruß Tino


  

Betrifft: AW: Bereich selektieren von: Dirk N.
Geschrieben am: 21.08.2008 21:51:41

Hi Tino,

gerade wollte ich dir auch schreiben (Telepathie ??)...

Ich entdeckte meinen Fehler:
In dem Sheet gibt es keine farbliche Markierung - dies hatte ich nur zur Veranschaulichung so dargestellt u. dies war bestimmt auch irritierend - SORRY...

Deine Idee ist aber auch nicht von der Hand zu weisen - kann ich bestimmt mal gebrauchen !

Also trotzdem besten Dank.

MfG Dirk


  

Betrifft: Zuverlässigkeit von UsedRange von: Jürgen
Geschrieben am: 21.08.2008 22:51:55

Hallo Dirk,

seinen schlechtem Ruf zum Trotz funktioniert in Office 2003 UsedRange nach meinen Erfahrungen recht zuverlässig (rückt z.B. auch das Ergebnis von .specialcells(xlCellTypeLastCell) zurecht), daher wundere ich mich, dass es in Deinem Anwendungsfall den Bereich nicht korrekt zurückgibt.

Ich habe folgenden Test gemacht: ein Arbeitsblatt von A1 bis E10 gefüllt, dann den folgenden Code ausgeführt:


Sub test()
Debug.Print ActiveSheet.UsedRange.Address
Range("a1:a10").ClearContents
Range("a10:e10").ClearContents
Debug.Print ActiveSheet.Cells(1).SpecialCells(xlCellTypeLastCell).Address
Debug.Print ActiveSheet.UsedRange.Address
Debug.Print ActiveSheet.Cells(1).SpecialCells(xlCellTypeLastCell).Address
End Sub



Hier das (für UsedRange sprechende) Ergebnis:

$A$1:$E$10
$E$10
$B$1:$E$9
$E$9

Was ist also in Deinem Fall anders? Frage in die Runde: gibt es Erfahrungen, wann UsedRange (ab Excel 2003) bei zuvor gelöschten Zellen versagt?

Gruß, Jürgen


  

Betrifft: AW: Zuverlässigkeit von UsedRange von: Dirk N.
Geschrieben am: 22.08.2008 19:58:29

Hallo Jürgen,

bitte entschuldige meine späte Antwort - bin gerade erst nachhause gekommen...

Ich habe ohnehin noch andere Fragen zu VBA etc. u. mache gleich einen neuen Thread auf (Duplikate...).
Da lade ich auch eine Beispielmappe hoch. Mir ist dieses "Fehlverhalten" ein totales Rätsel, aber vllt. liegt ja auch die Ursache bei mir bzw. in den falsch verwendeten Codes...

MfG Dirk


 

Beiträge aus den Excel-Beispielen zum Thema "Bereich selektieren"