Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Changing Borders In A Document With Vba

  1. #1
    Join Date
    28th March 2007
    Posts
    3

    Changing Borders In A Document With Vba

    Hello,

    I have a report that is imported into excel with all thick borders. Does anyone know a VBA code (or simpler way) to change all of these borders to skinny borders? Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Changing Borders In A Document With Vba

    something like this should help you

    VB:
     
    Sub formatCurrentRegion() 
         
        Selection.CurrentRegion.Select 
         
        With Selection 
            .Borders(xlDiagonalDown).LineStyle = xlNone 
            .Borders(xlDiagonalUp).LineStyle = xlNone 
            .Borders(xlEdgeLeft).LineStyle = xlContinuous 
            .Borders(xlEdgeLeft).Weight = xlThin 
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic 
            .Borders(xlEdgeTop).LineStyle = xlContinuous 
            .Borders(xlEdgeTop).Weight = xlThin 
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic 
            .Borders(xlEdgeBottom).LineStyle = xlContinuous 
            .Borders(xlEdgeBottom).Weight = xlThin 
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic 
            .Borders(xlEdgeRight).LineStyle = xlContinuous 
            .Borders(xlEdgeRight).Weight = xlThin 
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic 
            .Borders(xlInsideVertical).LineStyle = xlContinuous 
            .Borders(xlInsideVertical).Weight = xlThin 
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic 
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous 
            .Borders(xlInsideHorizontal).Weight = xlThin 
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic 
        End With 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Changing Borders In A Document With Vba

    Or this:
    VB:
    Sub x() 
         
        Dim rCel As Range 
        Dim iEdge As Integer 
         
        Application.ScreenUpdating = False 
        For Each rCel In ActiveSheet.UsedRange 
            With rCel 
                For iEdge = xlDiagonalDown To xlEdgeRight 
                    If .Borders(iEdge).LineStyle <> xlLineStyleNone Then 
                        .Borders(iEdge).LineStyle = xlContinuous 
                        .Borders(iEdge).Weight = xlHairline 
                    End If 
                Next 
            End With 
        Next 
        Application.ScreenUpdating = True 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th March 2007
    Posts
    3

    Re: Changing Borders In A Document With Vba

    Thanks! This worked perfectly.

    Quote Originally Posted by shg
    Or this:
    VB:
    Sub x() 
         
        Dim rCel As Range 
        Dim iEdge As Integer 
         
        Application.ScreenUpdating = False 
        For Each rCel In ActiveSheet.UsedRange 
            With rCel 
                For iEdge = xlDiagonalDown To xlEdgeRight 
                    If .Borders(iEdge).LineStyle <> xlLineStyleNone Then 
                        .Borders(iEdge).LineStyle = xlContinuous 
                        .Borders(iEdge).Weight = xlHairline 
                    End If 
                Next 
            End With 
        Next 
        Application.ScreenUpdating = True 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Changing Borders In A Document With Vba

    Great, you're welcome.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. changing document name for printing
    By Zeno_nz in forum EXCEL HELP
    Replies: 2
    Last Post: February 23rd, 2005, 14:30

Bookmarks

Posting Permissions

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