+ Reply to Thread
Results 1 to 6 of 6

Use of Range and Last row to add borders around cells.

Hybrid View

  1. 05-18-2012, 04:34 PM #1
    BBoydAnchor
    BBoydAnchor is offline
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Use of Range and Last row to add borders around cells.

    After searching and reading some other posts similar to what I'm looking to do, I have decided that I could use a little help.

    The code I have right now that does not work is...
    Range("A1:H" & LastRow).Borders.LineStyle = xlContinuous
    What I'm doing to taking a report that is supply in a Word Document and analyzing it in Excel. The macro I have created removes columns that are not needed and inserts a few new ones. The sheet has columns from A:H and I would like to have borders around all cells both empty and with content. However, every time I need to do this the row count will change. I feel that I'm on the right track, but not sure what corrections I need to make for it to work.

    BTW... there is no hurry on this as I'm leaving the office soon and will not return until Monday.

    Thanks in advance.

    Brian
    Last edited by BBoydAnchor; 05-21-2012 at 03:06 PM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 05-19-2012, 03:12 AM #2
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Use of Range and Last row to add borders around cells.

    I tested you code snippet and it works fine.

    What kind of problem do you have with it? Do you get any error messages? Or do you want to get rid of "old" borders before you write new data to range A1:H & LastRow.

    If this is the case you could just add

    Range("A1:H" & LastRow).Borders.LineStyle = xlNone
    to get rid of "old" borders.

    Alf
    Reply With QuoteRegister To Reply
  3. 05-21-2012, 12:14 PM #3
    BBoydAnchor
    BBoydAnchor is offline
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use of Range and Last row to add borders around cells.

    I get a run-time error '1004': Method 'Range' of object '_Global' failed. Below is all the code. It was created as one would normally do by recording the steps and then I modified the code the best I knew how. There may even be some code that could be removed or modified to be more efficient.

    Sub List()
    Selection.ClearOutline
    Range("B:B,D:D,E:E,G:G,J:J,K:K").Delete Shift:=xlToLeft
    Range("A1").Formula = "Store"
    Range("B1").Formula = "Trip"
    Range("C1").Formula = "City"
    Range("D1").Formula = "Size"
    Range("E1").Formula = "Type"
    Range("F1").Formula = "Return"
    Range("G1").Formula = "Trimcap"
    Range("H1").Formula = "Notes"
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").ColumnWidth = 8
    Columns("G:G").ColumnWidth = 8
    Columns("H:H").ColumnWidth = 30
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .CenterFooter = "Page &P of &N"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintQuality = 600
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperLetter
    End With
    
    Cells.EntireRow.AutoFit
    Application.WindowState = xlMinimized
    ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
    
    Range("A1:H" & LastRow).Borders.LineStyle = xlContinuous

End Sub
    Reply With QuoteRegister To Reply
  4. 05-21-2012, 01:52 PM #4
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Use of Range and Last row to add borders around cells.

    You could try to change 

    Sub List()
    Selection.ClearOutline
    to 

    Sub List()
    Dim LastRow As Integer
    Selection.ClearOutline
    and also change 
    Range("A1:H" & LastRow).Borders.LineStyle = xlContinuous
    to

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:H" & LastRow).Borders.LineStyle = xlContinuous
    Alf
    Last edited by Alf; 05-21-2012 at 01:56 PM.
    Reply With QuoteRegister To Reply
  5. 05-21-2012, 03:05 PM #5
    BBoydAnchor
    BBoydAnchor is offline
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use of Range and Last row to add borders around cells.

    Thank you Alf. This works perfectly.
    Reply With QuoteRegister To Reply
  6. 05-22-2012, 01:38 AM #6
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Use of Range and Last row to add borders around cells.

    Glad to be of help.

    Alf
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1