Excel VBA alle nicht verwendeten Spalten und Zeilen ausblenden und nur verwendete einblenden.

Hallo Armin,

du machst das Ganze etwas zu kompliziert.

Lese den UsedRange aus und splitte in auf in ein Array. Blende dann nur die nicht benutzten Zeilen und Spalten aus:

Sub Ausblenden()

Dim strUR As String

Dim LRow As Long

Dim LCol As Integer

Dim varTmp As Variant
With ActiveSheet

   strUR = .UsedRange.Address

   varTmp = Split(strUR, ":")

   LRow = Split(varTmp(1), "$")(2)

   LCol = Columns(Split(varTmp(1), "$")(1)).Column

   .Rows(LRow + 1 & ":1048576").Hidden = True

   .Range(.Cells(1, LCol + 1), .Cells(1048576, 16384)) _

      .EntireColumn.Hidden = True

End With

End Sub

Sub Einblenden()

With ActiveSheet.Range("A1:XFD1048576")

   .EntireRow.Hidden = False

   .EntireColumn.Hidden = False

End With

End Sub

Claus

Sheets("Test").Range(UsedRange.Address).EntireColumn.Hidden = False

Mal 'ne blöde Frage:

Du referenzierst hier explizit auf Sheets("Test"), aber den
UsedRange nicht...

Der Code geht sowieso nur in einem Codemodul einer Tabelle...

Der Code ist nicht zufälligerweise in einem Codemodul einer anderen Tabelle?

Ich tippe mal ja, denn das würde das beschriebene Verhalten erklären.

Andreas.

Ja du hast recht, dass ich einmal explizit referenziere und einmal nicht. Das find ich selbst ein wenig "seltsam", ist hier aber relativ trivial, da es nicht darum geht einen "perfekten" Code zu schreiben, sondern nur Übungszwecken dient. Ich finde es merkwürdig,
dass ich Dinge explizit referenzieren kann, aber auch "Unterelemente" einfach direkt ansprechen kann. Da weiß man eigentlich nicht immer unbedingt direkt zu welchem "Überobjekt" das Element, das man anspricht, eigentlich gehört.

Und nein, der Code steht im selben Sheet. Die richtige Lösung wäre - denke ich - wenn man Variablen erstellt und die Dinge die man anspricht diesen Variablen zuordnet. Das hab ich hier in der "QuickAndDirty" Variante jedoch bewusst ausgelassen. Und wie bereits
erwähnt, war mein Fehler, dass das UsedRange.Address-Objekt seinen Wert zur Laufzeit ändert.

Gruß Armin

Sheets("Test").Range(UsedRange.Address).EntireColumn.Hidden = False

Mal 'ne blöde Frage:

Du referenzierst hier explizit auf Sheets("Test"), aber den
UsedRange nicht...

Der Code geht sowieso nur in einem Codemodul einer Tabelle...

Der Code ist nicht zufälligerweise in einem Codemodul einer anderen Tabelle?

Ich tippe mal ja, denn das würde das beschriebene Verhalten erklären.

Andreas.

Hallo Claus,

ich habe eben noch mal mit meinem ursprünglichen Code gespielt und auch eine zusätzliche Sub ausprobiert.

Wenn ich den zu bearbeitenden Bereich direkt als String eingebe funktioniert alles wie gewollt. Sobald ich aber UsedRange.Address verwende, passieren wieder merkwürde Dinge.

Also habe ich UsedRange.Address in eine eigene Variable am Code-Anfang meiner ursprünglichen Sub gepackt und siehe da, UsedRange.Address ändert sich zur Laufzeit.

Hier ein Ausschnitt:

    Dim strUsedRangeAddress As String

    strUsedRangeAddress = Sheets("Test").UsedRange.Address

    [b]Debug.Print Time & " " & strUsedRangeAddress[/b]

    Sheets("Test").Range("$A$1:$XFD$1048576").EntireColumn.Hidden = False

    Sheets("Test").Range("$A$1:$XFD$1048576").EntireRow.Hidden = False

    [b]Debug.Print Time & " " & UsedRange.Address[/b]

Ergebnis im Direktbereich:

14:50:53 $A$1:$I$[b]30[/b]

14:50:53 $A$1:$I$[b]18[/b]

Die erste Zeile ist der Inhalt meiner Variablen und die zweite der von UsedRange.Address. (Am Inhalt der Zellen im Sheet wurde nichts geändert!)

Das "Witzige" daran ist, dass ich den Code so oft hinter einander ausführen kann wie ich will. Ich erhalte immer dieselben Ergebnisse im Direktfenster (Außer für die Uhrzeit - die ist immer aktuell ;-) ).

Also ja, ihr habt schon recht mit der Ermittlung des benutzten Bereiches. Die Quintessenz ist, dass man sich nicht auf UsedRange.Address verlassen darf, da dieses zur Laufzeit seinen Wert ändert und einen somit in die Irre führt.

Problem solved. Danke für eure Zeit und eure Mühe.

Zu deiner Meldung Claus.

Genau deswegen (falsche Darstellung) blende ich erst alles ein und anschließend wieder aus. Wenn ich das nicht getan hatte, hatte ich ebenfalls Darstellungsfehler. Ich denke du hast nicht ganz Unrecht, wenn man die Bereiche richtig
ermittelt und sich nicht - wie ich - auf UsedRange.Address verlässt, dann wird deine Variante wohl auch funktionierten. Wobei du bei deiner Variante noch berücksichtigen müsstest, dass auch Zellen über und links vom benutzten Bereich unbenutzt sein könnten.

Gruß Armin.

Hallo Andreas,

ja das stimmt schon, dass UsedRange.Address nicht unbedingt den richtigen Inhalt hat.

Darauf will ich aber auch gar nicht hinaus. Worauf ich hinaus will ist, wenn ich

    '' Nur die benutzten Zeilen einblenden    Sheets("Test").Range(UsedRange.Address).EntireRow.Hidden = False    '' Nur die benutzten Spalten einblenden

    Sheets("Test").Range(UsedRange.Address).EntireColumn.Hidden = False

einen String mit gebe, der den genau zu bearbeiteten Bereich festlegt, dann erwarte ich von Excel, dass eben genau dieser Bereich entsprechend bearbeitet wird. Genau das passiert aber
nicht immer.

Ich könnte ja statt UsedRange.Address auch einfach selbst den String "$B$5:$J$13" eingeben.

Dann müssten die Spalten B bis J und die Zeilen 5 bis 13 eingeblendet werden. So wie ich Excel erlebe könnte es funktionierten, vielleicht auch nicht und ich bekomme nur B5 bis E12 angezeigt.

Du hast schon recht mit dem Argument, dass eine Zelle auch "leer" sein könnte und einfach nur eine Hintergrundfarbe hat. In dem Fall wäre das für mich auch eine benutzte Zelle. Aber auch dies ist für mich an der Stelle ersteinmal
trivial. Mir geht es explizit darum, dass ich einem Funktionsaufruf ein eindeutiges Argument übergebe, eine bestimmte Erwartungshaltung an die Umsetzung habe und dies Mal geschieht und Mal nicht.

Es ist mir an der Stelle völlig egal wie der String zustande kommt oder ob er den richtigen Bereich beschreibt, solange er das benötige Format hat.

"$Spaltevon$Zeilevon:$Spaltebis$Zeilebis"

Ob er den richtigen Bereich angibt ist wieder mein Problem. Aber ich erwarte von Excel, wenn ich sage, blende mir von hier bis da ein/aus, dass dann auch genau das getan wird.

Ich vermute die Lösung des Problems liegt ganz einfach darin, dass man wirklich jede Spalte/Zeile einzeln auf Benutzung (welcher Art auch immer) überprüfen muss und dann einzeln ein und ausblenden lassen muss.

Gruß Armin