Border Last Range Of Cells

Tigger · Aug 2nd 2008

Hi,

I am trying to have a border drawn around a group of cells. This group of cells changes per list I make, but only the height changes. The width is always the same (from column A to column J) and the top is always at row 5. Only the last row changes. How can a border be drawn around this group of cells no matter the size of the group of cells? Below is the macro I have written so far.

Code

Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=7
    Columns("K:N").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll ToRight:=-7
    Range("A2").Select
    Rows("2:2").RowHeight = 49.5
    lastrow1 = Range("A65536").End(xlUp).Select
    Range("A5:J" & lastrow1).BorderAround.Weight = xlThin
    lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row
    For I = lastrow2 To 2 Step -1
    If Cells(I, "C").Value <> Cells(I - 1, "C").Value Then Rows(I).Insert
    Next I
    
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    End Sub

Display More

This line of code does not work

Code

Range("A5:J" & lastrow1).BorderAround.Weight = xlThin

and I can't come up with an answer that does work.

Any help is much appreciated!

Cheers, Tom

VBA Noob · Aug 2nd 2008

Re: Bordering A Changing Group Of Cells

Try avoid selecting. Below suggestions to avoid selecting and amend code for border

Code

Dim LastRow1 As Long
LastRow1 = Cells(Rows.Count, "A").End(xlUp).Row
Range("A5:J" & LastRow1).BorderAround _
Weight:=xlThin

Code

Cells.Select ' Not Required
Cells.EntireColumn.AutoFit 
ActiveWindow.SmallScroll ToRight:=7 'Not Required
Columns("K:N").Select  ' Change this line and
Selection.ClearContents ' this one to Columns("K:N").ClearContents
ActiveWindow.SmallScroll ToRight:=-7 'Not Required
Range("A2").Select  'Not Required
Rows("2:2").RowHeight = 49.5 
lastrow1 = Range("A65536").End(xlUp).Select 
Range("A5:J" & lastrow1).BorderAround.Weight = xlThin 
lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row 
For I = lastrow2 To 2 Step -1 
    If Cells(I, "C").Value <> Cells(I - 1, "C").Value Then Rows(I).Insert 
Next I 
 
With ActiveSheet.PageSetup 
    .Orientation = xlLandscape 
    .PaperSize = xlPaperA4 
    .FirstPageNumber = xlAutomatic 
    .FitToPagesWide = 1 
    .FitToPagesTall = 1 
    .PrintErrors = xlPrintErrorsDisplayed 
End With 
 
End Sub

Display More

VBA Noob

Tigger · Aug 2nd 2008

Re: Bordering A Changing Group Of Cells

Thanks VBA Noob, that works really well! BorderAround is an excellent command!

Cheers, Tom

Border Last Range Of Cells

Participate now!

Share