Consulting

Results 1 to 5 of 5

Thread: AutoFit issue

  1. #1
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

    AutoFit issue

    Hi Folks

    I'm trying to use the autofit method on a cell range using VBA, however, it seems to have no effect.

    Consindering rows, the problem seems to be the same as if doing a double-click on the header between rows...sometimes it does autofit and other times not. So, I'd like to understand why it doesn't auto-adjust to the needed height at times, while it does work sometimes.

    My guess is that once the row height in question has been set, there is no way back; and then the autofit method gets useless. but i'm hoping someone will tell me i'm wrong

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    "My guess is that once the row height in question has been set, there is no way back; and then the autofit method gets useless. but i'm hoping someone will tell me i'm wrong"

    If I correctly understand your comment .... you are correct. Once a row or a column has been adjusted to fit the data within, the autofit command doesn't have an effect.

  3. #3
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    Somehow the row must be flagged then as "adjusted". Is there a way to get it "unflagged" again?

    In my case, moreover, the cell in question is a merged range of 4 cells in a row, with text wrapping set to true. Apparently autofit doesn't work on merged cells.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    I replicated what you described.

    Although I haven't found a workaround yet, I'll continue to search. Or perhaps someone else on the FORUM has a solution ?

    
    Sub autofitwidth()
    Dim i As Integer
    Cells.EntireColumn.AutoFit
        For i = 1 To ActiveSheet.UsedRange.Columns.Count
            Columns(i).ColumnWidth = Columns(i).ColumnWidth
        Next i
    
    
    Worksheets("Sheet1").Range("A:A").EntireRow.AutoFit
    End Sub
    Attached Files Attached Files

  5. #5
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    I've found a workaround; it's not very elegant, but it works

    - First I loop through the merged range (all in a single row) to get the columnwidth property of each of the cells
    - Then I copy the merged range to a standard cell and set it's column width to the width of the merged range to get the correct row height (with text wrapping set to true)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •