VBA: letzte belegte Zeile und Spalte einer Tabelle ermitteln

[gehtnetgibtsnet]

Hallo Forum,

mit folgenden Befehlen ermittle ich die Eckpunkte meiner Tabelle:

MaxCols = ActiveSheet.UsedRange.Columns.Count
MaxRows = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

Wenn ich dann anschließend den ermittelten Bereich durch

Range(Cells(1,1),Cells(MaxRows,MaxCols)).Select

sichtbar mache, stelle ich bei vielen meiner Tabellen fest, dass in diesem Bereich (jeweils am Ende) unbenutzte Zeilen bzw. Spalten enthalten sind.

Jetzt möchte ich überprüfen, ob jeweils am Ende ungenutzte Bereiche vorhanden sind. Deshalb habe ich mit folgenden Code das Ende jeder Spalte bzw. jeder Zeile ermittelt:

Sub die_Letzten()
MaxCols = ActiveSheet.UsedRange.Columns.Count
MaxRows = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
Range(Cells(1,1),Cells(MaxRows,MaxCols)).Select
MaxZeile = 0
MaxSpalte = 0
For Loop1 = 1 To MaxCols
LZeile = Cells(Rows.Count, Loop1).End(xlUp).Row
If LZeile > MaxZeile Then MaxZeile = LZeile
Next Loop1
For Loop1 = 1 To MaxRows
LSpalte = Cells(Loop1, Columns.Count).End(xlToLeft).Column
If LSpalte > MaxSpalte Then MaxSpalte = LSpalte
Next Loop1
Debug.Print "MaxRows = " & MaxRows
Debug.Print "MaxCols = " & MaxCols
Debug.Print "L. Zeile = " & MaxZeile
Debug.Print "L. Spalte = " & MaxSpalte
End Sub

Ergebnis:

MaxRows = 100
MaxCols = 22
L. Zeile = 106
L. Spalte = 19

Leider sind die ermittelten Werte nicht korrekt, und ich habe auch die Ursache gefunden: in manchen Zeilen sind die letzten Spalten miteinander verbunden, und in den dazugehörenden Spalten ist in keiner anderen Zeile ein Wert vorhanden.

Und in Zeile 106 ist eine Hintergrundfarbe eingetragen!

Hat jemand eine Idee zu Lösung des Problems?

 

[Pyrukar]

die offensichtliche lösung wäre wohl, die verbundenen zeilen wieder zu trennen ... aber ich schätze mal das suchst du nicht, oder?

ich hab das eigentlich immer etwas dilletantisch gelöst mit
j= 'Anfang'
do until Cells(i,j)=""
j=j+1
loop

'letzte nichtleere Spalte ist dann (i,j)

Keine Ahnung, ob das mit Verbundenen Zellen funktioniert ... ich würde mal tippen: nein ... ich glaube aber mit Zellenverbinden und VBA tut man sich allgemein keinen gefallen

 

[Skidrow1988]

Zellenverbinden ist in VBA kacke. Für VBA ist in dem Moment die Zelle mit Werten gefüllt die als erstes kommt. Die zweit, also verbundene Zelle, ist leer. Du könntest hingehen und überprüfen ob links davon nicht etwas steht.

Ergänzung (19. Februar 2020)

Oder du prüfst, ob es eine verbundene Zelle ist.

Das machst du mit .MergeCells

range("A1").MergeCells

 

[Sun_set_1]

Versuch mal die beiden. Das Problem ist das du Cells() verwendest zur Ermittlung und in der Cells mit Rows arbeitest.

Du könntest hingehen und überprüfen ob links davon nicht etwas steht.

tadaaa

oTargetRange = oTargetSheet.Cells(1, Columns.Count).End(xlToLeft).Offset()
lngColumns = oSourceBook.Sheets(1).Cells(1, 1).CurrentRegion.Columns.Count

 

[gehtnetgibtsnet]

@Pyrukar

Deine Methode geht davon aus, dass in jeder Zelle etwas steht; das ist bei mir nicht der Falll, weshalb Dein Code für mich nicht einsetzbar ist.

@Skidrow1988

Dein Hinweis mit .mergecells war gut. Allerdings liefert das nur Wahr oder Falsch zurück. Aber ich habe dann noch mergearea.address gefunden. Damit bekommt man den Range der angegebenen Zelle - ob verbundenen oder nicht. Dabei ist es egal, welche Zelle eines Verbundes man dabei angibt; es kommt immer die gleiche Antwort zurück.

Wenn ich jetzt von der letzten - nach meiner bisherigen Methode - gefundenen Zelle in der Zeile oder Spalte die mergearea.address ermittle, erhalte ich den zugehörigen Range.

Mit Range("$P$65:$Q$65").Rows.Count erhalte ich die Anzahl der Zeilen, und mit Range("$P$65:$Q$65").Columns.Count die Anzahl der Spalten. Den entsprechenden Wert - 1 muss ich jetzt nur noch zu einem zuvor ermittelten Wert addieren, je nachdem, nach was ich aktuell gesucht habe.

@Sun_set_1

Sorry; mit Deinem Code komme ich nicht weiter. Du verwendets Variablen, von denen nicht bekannt ist, welchen Inhalt sie haben!

Fazit: der Weg mit mergearea.address scheint der beste (für mich) zu sein.

Danke für Euere Antworten.

 

[Skidrow1988]

Schön daß man dir helfen konnte. Kannst dich gerne nochmal melden, wenn etwas nicht klappen sollte.

 

[Sun_set_1]

@gehtnetgibtsnet

Sorry, dachte dass die sprechend sind. oTargetRange= Zielblatt auf das kopiert werden soll, oSourceBook der Quellsheet von dem aus kopiert wird.

Zielbereich wird das gesamte Blatt markiert (Cells 1,1) .End(xlToLeft).Offset()
Quellbereich wird bestimmt durch Cells(1,1).CurrentRegion.Columns.Count

Also markiert von A1 bis zur letzten benutzen Zelle der letzten Spalte.

CurrentRegion ist quasi der neue UsedRange, der Dir ja fehlt. Siehe auch

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns.

Hier ist der Unterschied zu UsedRange. CurrentRegion wird nicht durch die letzte beschriebene Zelle bestimmt, sondern durch die erste Kombination aus einer komplett leeren Reihe und einer komplett leeren Zeile, eingegrenzt. Zusammen mit dem Offset Befehl, kann das genau dein Problem mit den verbundenen Zellen adressieren.

Oder der ist gar nicht nötig, da die erste leere Zeile und Spalte eventuell die neben/unter der letzten verbundenen Zelle ist. Also wenn die verbundenen Zellen bspw. E4+F4 sind, müsste die ermittelte erste leere Zeile 5 und die erste leere Spalte G sein. Die bestimmte CurrentRegion also A1:F4. Wenn nicht (also A1:E4 rauskommt) einfach mit Offset arbeiten.

This property is useful for many operations that automatically expand the selection to include the entire current region.

https://docs.microsoft.com/de-de/office/vba/api/excel.range.currentregion

In dem MS-Beispiel wird mit negativem Offset die Kopfzeile abgeschnitten. Mit positivem Offset kann also entsprechend um eine erweitert werden, was dein Problem der verbundenen Zellen variabel adressiert. ColloumsCount findet die letzte verbundene Zelle am Rand, positiver Offset erweitert dann halt um die eine Zeile/Spalte.

Hoffe, nun ist es etwas klarer