Announcement

Collapse
No announcement yet.

Borders in VBA code into worksheet

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

  • Borders in VBA code into worksheet



    I am trying to set a border on a summery workshhet where the rows will vary so I need this to border to the last row. The border I need are columns (A),(C),(E).(G) and I - Dashed starting at (A7) and ending at last row with data between A:I Columns (B), (d),(F) and (h) will not be bordered. I would like outside edge of column (A) and (I) double lined as well as the bottom of the last row.

    Can anyone help!

  • #2
    Re: Borders in VBA code into worksheet

    Get your Border code using the Macro Recorder, then determine the cells that ned it with something like
    Code:
    Dim r as long
    r=Range(A7").end(xlDown).Row
    
    Cells(r,1) ' cell to border
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Borders in VBA code into worksheet

      I tried this code however I get a complile error when attempting to run

      Code:
      Sub Border()
      
          Dim r As Long
          r = Range("A7").End(xlDown).Row
          Range.Cells(r,1)
          Selection.Borders(xlDiagonalDown).LineStyle = xlNone
          Selection.Borders(xlDiagonalUp).LineStyle = xlNone
          With Selection.Borders(xlEdgeLeft)
              .LineStyle = xlContinuous
              .Weight = xlHairline
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeTop)
              .LineStyle = xlDouble
              .Weight = xlThick
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeBottom)
              .LineStyle = xlDouble
              .Weight = xlThick
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeRight)
              .LineStyle = xlContinuous
              .Weight = xlHairline
              .ColorIndex = xlAutomatic
          End With
          Range.Cells(r,3)
          Selection.Borders(xlDiagonalDown).LineStyle = xlNone
          Selection.Borders(xlDiagonalUp).LineStyle = xlNone
          With Selection.Borders(xlEdgeLeft)
              .LineStyle = xlContinuous
              .Weight = xlHairline
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeTop)
              .LineStyle = xlDouble
              .Weight = xlThick
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeBottom)
              .LineStyle = xlDouble
              .Weight = xlThick
              .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeRight)
              .LineStyle = xlContinuous
              .Weight = xlHairline
              .ColorIndex = xlAutomatic
          End With

      Comment


      • #4
        Re: Borders in VBA code into worksheet

        Hi,

        Yes Range.Cells(r,1) is invalid.
        Try this, I have put the bordering code into a seperate routine to which you pass the cell.
        Sub Border()

        Dim r As Long
        r = Range("A7").End(xlDown).Row

        ApplyBorder Range("A" & r)
        ApplyBorder Range("C" & r)
        ApplyBorder Range("E" & r)
        ApplyBorder Range("G" & r)

        End Sub
        Sub ApplyBorder(ToRange As Range)

        With ToRange
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
        End With
        End With

        End Sub

        Cheers
        Andy

        Comment


        • #5
          Re: Borders in VBA code into worksheet

          It doesn't look like you are actually selecting the cells.
          Code:
          Sub Border() 
               
              Dim r As Long 
              r = Range("A7").End(xlDown).Row 
             Cells(r,1).Select 
              Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
              Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
              With Selection.Borders(xlEdgeLeft) 
                  .LineStyle = xlContinuous 
                  .Weight = xlHairline 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeTop) 
                  .LineStyle = xlDouble 
                  .Weight = xlThick 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeBottom) 
                  .LineStyle = xlDouble 
                  .Weight = xlThick 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeRight) 
                  .LineStyle = xlContinuous 
                  .Weight = xlHairline 
                  .ColorIndex = xlAutomatic 
              End With 
              Cells(r,3).Select 
              Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
              Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
              With Selection.Borders(xlEdgeLeft) 
                  .LineStyle = xlContinuous 
                  .Weight = xlHairline 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeTop) 
                  .LineStyle = xlDouble 
                  .Weight = xlThick 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeBottom) 
                  .LineStyle = xlDouble 
                  .Weight = xlThick 
                  .ColorIndex = xlAutomatic 
              End With 
              With Selection.Borders(xlEdgeRight) 
                  .LineStyle = xlContinuous 
                  .Weight = xlHairline 
                  .ColorIndex = xlAutomatic 
              End With
          Last edited by royUK; April 2nd, 2006, 01:45.
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment


          • #6
            Re: Borders in VBA code into worksheet

            OK guys,

            this bordered only the last row of the worksheet. I need all the rows bordered to the last row with data. The format seems to be correct though

            Comment


            • #7
              Re: Borders in VBA code into worksheet

              Assuming not every row should have the double border.
              Sub Border()

              Dim r As Long
              r = Range("A7").End(xlDown).Row

              ApplyBorder Range("A7", "A" & r)
              ApplyBorder Range("C7", "C" & r)
              ApplyBorder Range("E7", "E" & r)
              ApplyBorder Range("G7", "G" & r)

              End Sub

              Cheers
              Andy

              Comment


              • #8


                Re: Borders in VBA code into worksheet

                Thanks for the help guys, this worked out well!!!!!!!!!!!!

                Comment

                Working...
                X