Announcement

Collapse
No announcement yet.

Changing Borders In A Document With Vba

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Changing Borders In A Document With Vba

    something like this should help you

    Code:
    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

    Comment


    • #3
      Re: Changing Borders In A Document With Vba

      Or this:
      Code:
      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
      Entia non sunt multiplicanda sine necessitate.

      Comment


      • #4
        Re: Changing Borders In A Document With Vba

        Thanks! This worked perfectly.

        Originally posted by shg
        Or this:
        Code:
        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

        Comment


        • #5
          Re: Changing Borders In A Document With Vba

          Great, you're welcome.
          Entia non sunt multiplicanda sine necessitate.

          Comment

          Working...
          X