In Excel Spaltenbreite mittel VBA Code?

Hallo, ich habe einen VBA Code wo ich Spalten aus einer Excel- Datei nach Word kopiere. Jetzt möchte ich den Spalten unterschiedliche Längen vorgeben.

Den Code zum kopieren ist folgender:

    With liO.Range
      .Columns.Hidden = True                'jetzt werden erst mal alle Spalten ausgeblendet
      .Columns(2).Hidden = False    'Nummer           'und hier die Spalten die du brauchst wieder eingeblendet
      .Columns(4).Hidden = False    'Name
      .Columns(5).Hidden = False    'Vorname
      .Columns(13).Hidden = False   'gen_bis
      .Columns(14).Hidden = False   'Gruppe
      .Columns(19).Hidden = False 'Krankenkasse
      .Columns(21).Hidden = False  'Bemerkung
      
       '.Columns("B:O").EntireColumn.AutoFit 'geht so nicht
      
    End With

Wie muss ich den Code veräandern/ergänzen um für jede Zelle eine individuelle länge der Zelle zu erhalten? Gruß

Answer 1

[Kelrycorfg]

Interessant. Wie gesagt, bei mir funktioniert der Header-Parameter wie er soll. Das muss also entweder mit deiner Tabelle, oder aber mit dem Objekt deines With-Blockes zusammenhängen.

Du hast aber nicht rein zufällig noch offene SortFields definiert? Die können dazwischenfunken. Vielleicht aus vorherigen Versuchen noch? Wenn du die nicht manuell über "benutzerdefiniertes Sortieren" löschst, oder die mittels VBA Code mit x.SortFields.Clear entfernst, bleiben die in der Tabelle erhalten. Das hat bei mir auch mal nen Überlauf produziert, weil ich ständig SortFields.Add gemacht habe bis Excel irgendwann der Meinung war, mehr ging nicht. War aber mein eigener Handlingsfehler.

Hinweis zu deinem Code, falls dir der so reicht, du hast den Key3 zweimal definiert. Der dürfte bei dir also nicht korrekt sortieren wenn du den so lässt.

Answer 2

[Kelrycorfg]

Individuelle Längen von Zellen gibt es nicht. Du meinst sicherlich aber Spaltenbreiten.

Sollte eigentlich mit der Eigenschaft ColumnWidth funktionieren und dem Wunschwert.

Bspw.

.Columns(4).ColumnWidth = 15.14

Comments

[Wolly25]

Danke ,aber das hatte ich schon versucht. Hinweis habe die Tabelle formatiert, hat das etwa Auswirkungen?

[Kelrycorfg]

@Wolly25

Das kann sogar sein. Ich denke, dein Range-Objekt wird das blockieren.

Was auf jeden Fall funktioniert ist der Direktzugriff:

Columns(1).ColumnWidth = 15.14

Das formatiert die Spaltebreite der 1. Spalte. Geht bei mir auch mit formatierten Bereichen.

[Wolly25]

@Kelrycorfg

Danke, das funktioniert . Aber ich habe noch ein Problem. Wenn ich die Tabelle aufsteigend sortiere werden mir die Überschriften (1te Zeile) bei "Name" einsortiert. Habe beim Formatieren "enthält Überschriften" an gehackt.

Hier mal mein Code dazu:

Public Sub recalcRowNr()
    Dim LastRow As Long ' Tabelle ArbDat sortieren
    Dim i
    With Worksheets("ArbTab")
        Worksheets("ArbTab").Unprotect  ' HIERMIT WIRD DIE TABELLE "ARBDAT" FÜR DEN ZUGRIFF GEÖFFNET
        LastRow = .Range("A65536").End(xlUp).Row
        .Range("A2:z" & LastRow).Sort Key1:=.Range("D2"), Key3:=.Range("E2"), Key3:=.Range("H2")

        Application.EnableEvents = False
       For i = 2 To LastRow
            .Range("A" & i) = i - 1
        Next
        Application.EnableEvents = True

        Call UserForm_Initialize
    End With
End Sub

[Kelrycorfg]

@Wolly25

Hm, das ist merkwürdig. Ich nehme mal an, deine Überschriften sind Zeile 1 (also A1 ist Z1. Der Datebereich, dann ab A2 bis Z2 bis runter (wertest du ja dynamisch korrekt aus). Wenn dem so ist, verstehe ich auch nicht, warum er dir die Überschriften aus der Zeile 1 mitsortiert.

Sollte die Überschrift allerdings in Zeile 2 stehen, dann noch musst du die dritte Zeile deines With-Blocks entsprechend anpassen.

[Kelrycorfg]

@Wolly25

So, habs. Dir fehlt in der Zeile der Hinweis, dass es eine Überschrift gibt.

Bei mir funktioniert diese Variante problemlos:

Range("A2:C4").Sort Key1:=Range("A1"), Key2:=Range("B1"), Header:=xlYes

Mein Datenbereich ist A2 bis C4, Überschriften in A1 bis C1. Hat er sauber nach Key1 und Key2 sortiert.

Das musst du jetzt nur analog zu deinem Bereich adaptieren, dann solte es klappen.

[Wolly25]

@Kelrycorfg

Hallo, danke für die Tipps. Musste den Code in Spalte "B" beginnen weil in Spalte "A" eine PosNr immer mit aktualisiert wird. Nochmal Gruß und Danke

[Wolly25]

@Wolly25

Hallo! Hab mich jetzt mal dem Sortieren näher beschäftigt.
Also wenn ich das "Header:=xiYes weg lasse sortiert der Code. Aber die Überscrift nimmt er ebenfalls mit, je nach dem ich eine Spalte auswähle.  
Weis hier jemand einen Rat? Gruß

Public Sub recalcRowNr()
    Dim LastRow As Long ' Tabelle ArbDat sortieren
    Dim i
    With Worksheets("ArbTab")
        Worksheets("ArbTab").Unprotect  ' HIERMIT WIRD DIE TABELLE "ARBDAT" FÜR DEN ZUGRIFF GEÖFFNET
        LastRow = .Range("A65536").End(xlUp).Row
        
        ' .Range("A1:z" & LastRow).Sort Key1:=.Range("D2"), Key3:=.Range("E2"), Key3:=.Range("H2")
        
'       .Range("B2:AF" & LastRow).Sort Key1:=Range("D2"), Header:=xlYes    ' Key2:=Range("C2"), Header:=xlYes

.Range("A2:Z240").Sort Key1:=Range("A1"), Key2:=Range("B1"), Header:=xlYes

'        Call UserForm_Initialize
    End With
End Sub

[Kelrycorfg]

@Wolly25

Ich habe deine Tabelle mal nachgebaut damit ich deinen Code 1:1 umsetzen kann.

Bei mir sortiert er richtig sobald ich die Zeile 1 mit in den Datenbereich mit reinnehme. Das Header zieht Zeile 1 dann wohl selbst ab.

.Range("A1:Z240").Sort Key1:=Range("A1"), Key2:=Range("B1"), Header:=xlYes

Du kannst auch gerne die Punkte vor das Range machen, damit du dein Objekt aus dem With-Block auch ansprichst.

.Range("A1:Z240").Sort Key1:=.Range("A1"), Key2:=.Range("B1"), Header:=xlYes

Das macht er genauso gut bei mir und ist in deinem Fall tatsächlich besser.

Wenn das bei dir immer noch nicht richtig funktioniert, dann muss irgendwas an deinen Excel-Einstellungen oder deiner Excel-Version (hab die 2016-er) anders sein als bei mir.

[Wolly25]

@Kelrycorfg

Danke für dein Testen. Habe jetzt folgendes gemacht:

1. Die Tabelle ab "A2" bis .... Formatiert
2. Hat Überschriften "markiert"
3. Die Sortierung ab der Zeile "A2" begonnen
4. Den Punkt vor dem Range gesetzt
5. Die Endung "Header:=xlYes" hat in meiner formatierten Datei die Überschriftenzeile in die Sortierung mit übernommen, somit ist sie weiter nach unten gerutscht. (Also nicht ignoriert)

Gruß Wolly

• Die Textzeile sieht jetzt wie folgt aus

.Range("A2:z" & LastRow).Sort Key1:=.Range("D2"), Key3:=.Range("E2"), Key3:=.Range("H2")

[Wolly25]

@Wolly25

Hallo, hab natürlich noch etwas weiter im Forum geschaut, und folgendes entdeckt:

Man kann mit der Endung Header:=xlNo die Überschrift ignorieren.

Oder mit

Header:=xlGuess

den automatischen Ausschluss der Überschrift festlegen.

Ich hätte noch eine Frage die ich aber mit einer Datei zur Anschauung verknüpfen müsste. Ich sehe hierfür aber in diesem Forum keine Möglichkeit. Geht hier so was?

[Wolly25]

@Wolly25

Hallo, habe meinen Fehler den ich eingebaut hatte (kopieren) gefunden.

Einen schönen Sonntag noch und Gruß Wolly

[Kelrycorfg]

@Wolly25

Hauptsache es funktioniert, dir auch einen schönen Sonntag noch :D