Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Borders in VBA code into worksheet

  1. #1
    Join Date
    7th March 2006
    Posts
    20

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,448

    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
    VB:
    Dim r As Long 
    r=Range(A7").end(xlDown).Row 
     
    Cells(r,1) ' cell to border
    
    
    Hope that Helps

    Roy

    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.

  3. #3
    Join Date
    7th March 2006
    Posts
    20

    Re: Borders in VBA code into worksheet

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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.
    VB:
    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


  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,448

    Re: Borders in VBA code into worksheet

    It doesn't look like you are actually selecting the cells.
    VB:
    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 at 01:45.
    Hope that Helps

    Roy

    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.

  6. #6
    Join Date
    7th March 2006
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Borders in VBA code into worksheet

    Assuming not every row should have the double border.
    VB:
    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


  8. #8
    Join Date
    7th March 2006
    Posts
    20

    Re: Borders in VBA code into worksheet

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

    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. VBA Code For Applying Borders To Ranges
    By Abi in forum EXCEL HELP
    Replies: 6
    Last Post: March 16th, 2009, 18:59
  2. Replies: 5
    Last Post: October 3rd, 2006, 15:41
  3. Replies: 1
    Last Post: July 12th, 2006, 20:14
  4. Writing code to a worksheet code module
    By Derk in forum EXCEL HELP
    Replies: 14
    Last Post: October 29th, 2004, 23:49

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