Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA to autofit columns with added space
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to autofit columns with added space

    I am using a VBA to auto fit the widest entry for a range of columns. I am wondering if there is a way to add some space to the auto fit so the columns are not so tight to the text. If some one could lay out the format I would appreciate it.

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,604
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to autofit columns with added space

    Front space and back space : _)@_)

  3. #3
    New Member
    Join Date
    Apr 2016
    Location
    Boise,Idaho
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to autofit columns with added space

    Hi Shadkng.
    You could try something along this line.

    Code:
    Sub ColumnWidth()
    
    
    Columns("A:A").AutoFit
    Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 2
    
    
    End Sub
    This will work with one column at a time, and will not work with multiple selections.
    Last edited by ExcelEdge; Nov 4th, 2018 at 10:57 PM. Reason: Forgot To Add Deteails

  4. #4
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to autofit columns with added space

    I found this on a youtube video and it seems to work. But I don't really understand the code. Do you see any problem with it? Thanks

    Sub AUTOFIT_COLUMN()
    Application.ScreenUpdating = False
    Cells.EntireColumn.autofit
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
    Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
    Next i
    Application.ScreenUpdating = True
    End Sub

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA to autofit columns with added space

    What does this mean:

    This will work with one column at a time, and will not work with multiple selections.

    Do you mean if you select column A To G

    Or do you mean Column B Column G Column J all at once


    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA to autofit columns with added space

    Well this means it will do this on all columns with data.
    We can only help if you give more specific details.
    You never mentioned what column or only selected columns.



    Quote Originally Posted by Shadkng View Post
    I found this on a youtube video and it seems to work. But I don't really understand the code. Do you see any problem with it? Thanks

    Sub AUTOFIT_COLUMN()
    Application.ScreenUpdating = False
    Cells.EntireColumn.autofit
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
    Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
    Next i
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,886
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to autofit columns with added space

    @Shadkng
    Don't see any issues with that code....try it and see what it does.
    If you don't like the result, close the workbook without saving.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,604
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to autofit columns with added space

    Why not just format the column(s) with a leading and/or a trailing space?

  9. #9
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to autofit columns with added space

    That code seems to work perfectly as it only formats the columns that have data it them, I think. Thanks everyone for the input.

  10. #10
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to autofit columns with added space

    Hi, below is partial code related to the previous threads. I want range (H:X) to auto fit but also add padding. The loop part of the code adds the padding for all columns. Can we change the loop to add padding for only columns (H:X)? Thanks

    Code:
    Sub DETAIL_FORM2_COPYROW()
        Application.ScreenUpdating = False
        Sheets("DETAIL FORM2").Activate
        Range("A30:X1500").ClearContents
        Rows("29:29").Copy
        Rows("29:" & Range("B1").Value).Select
        Selection.PasteSpecial xlFormulas
        Selection.PasteSpecial xlFormats
        Range("H:X").Columns.AutoFit
        
        'For i = 1 To ActiveSheet.UsedRange.Columns.Count
            'Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
            'Next i

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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