Excel Macro Help - Borders

[sjohnson77]

Hi Everyone

I am experiencing an issue with a macro I created to help format some raw data sheets I have to deal with at work. The macro I have made is almost perfect, except I need the macro to detect the data range of the cells in use (as this changes with each RAW data file I receive) and put a thick line border around the outside of the data (not each cell individually). The column length always stays the same it is just the number of rows that changes depending on how much data I get.

Could anyone help to amend the below for me so that the border issue can be resolved?

Columns("A:D").Select
    Range("D1").Activate
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:5").Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select
    Selection.ClearContents
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:R").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:P").EntireColumn.AutoFit
    Columns("Q:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("T:AD").Select
    Selection.Delete Shift:=xlToLeft
    Range("B3:S76").Select
    Range("S3").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Columns("L:L").Select
    Selection.ColumnWidth = 14.14
    Columns("J:J").Select
    Selection.ColumnWidth = 13
    ActiveWindow.SmallScroll Down:=51
    Range("C78").Select
    Rows("77:77").RowHeight = 12.75
    Rows("77:92").Select
    Selection.Delete Shift:=xlUp
    Rows("4:79").Select
    Range("A79").Activate
    Selection.RowHeight = 12.75
    ActiveWindow.SmallScroll Down:=-102
    Range("B1:S1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Font.Size = 11
    Selection.Font.Size = 12
    Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

Any help would be greatly appreciated!

Thanks
Saul

 

[Alex Blakenburg]

I am a bit confused. The macro seems to delete all the data on the sheet, so how is the macro meant to work out what the last row used is ?

 

[sjohnson77]

Thank you for responding Alex!

The macro deletes certain columns of data which I do not use, but it still has 18 columns worth of data remaining after the macro runs. I just need to a way of the macro detecting automatically where the First/Last row/Column is and then drawing a border around it if possible?

I am not excel savvy so if you/someone could edit the VBA I pasted above to do that, it would be greatly appreciated!

 

[Marc L]

Hi,​

​

maybe just using the UsedRange property …​

 

[Alex Blakenburg]

I was going to use Marc L's suggestion of using Used Range but I don't know what the last part of your code is doing after the border has been applied.
Something in that is setting the UsedRange bigger than the data range. Its certainly impact the usedrange if I run the macro on the same sheet a second time.

Before the Macro runs, do you have a column that can be relied upon to always have the most rows in it.
(It can be the same no of rows as other columns but not less)

If you do add this to you macro right at the very beginning BUT replace the S with the Column letter of the column to use for working out the last row.

    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "S").End(xlUp).Row

Then in your code replace this:

Range("B3:S76").Select

With this:

    Range ("B3:S" & LastRow).Select

I'm in Sydney Australia and will be logging off for the night. Not sure if Marc can help you further if you can't work it out, otherwise I will have another look tomorrow.

 

[Marc L]

​

If UsedRange is not appropriate - working only with smart worksheet - so try with CurrentRegion …​

​

For further help as I'm a very beginner in guessing I need an attachment …​

 

[MARK858]

As a total guess maybe

Sub BorderaroundRange()
    Dim lr As Long, lc As Long, FirstCell As Range

    Cells.Borders.LineStyle = xlNone

    Set FirstCell = Cells.Find("*", Cells(Rows.Count, Columns.Count), xlValues, , xlByRows, xlNext)

    If Not FirstCell Is Nothing Then
    
        lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
        lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

        Range(FirstCell, Cells(lr, lc)).BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    End If

End Sub

Edit: in case you have a header row in row 1

Sub BorderaroundRange2()
    Dim lr As Long, lc As Long, FirstCell As Range, myRng As Range

    Set myRng = Range("A2:XFD1048576")
    
    myRng.Borders.LineStyle = xlNone

    Set FirstCell = myRng.Find("*", Cells(Rows.Count, Columns.Count), xlValues, , xlByRows, xlNext)
    
    If Not FirstCell Is Nothing Then
    
        lr = myRng.Find("*", , xlValues, , xlByRows, xlPrevious).Row
        lc = myRng.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

        Range(FirstCell, Cells(lr, lc)).BorderAround LineStyle:=xlContinuous, Weight:=xlThick
 
    End If

End Sub

 

[Alex Blakenburg]

I just need to a way of the macro detecting automatically where the First/Last row/Column is and then drawing a border around it if possible?

I am not excel savvy so if you/someone could edit the VBA

@sjohnson77, we need to know more detail on the requirements.
I can modify MARK858's solution to do your border part but what then happens when further down you delete Rows 77:92.
This seems to imply there is data below the bottom row of the border area. Is that the case ?

• Are you copying the data into an new empty sheet each time or alternatively running the macro against a new sheet created when you get the data ?
  ie do we need to clean up previous borders and merged cells or are we starting fresh each time.
  
  
• You refer to determining the First Row & Column but the macro seems to indicate that you want the border to start at B3 regardless.
  Do you want the macro start at the first cell with data in it ? (even if this is in column A and/or rows 1 & 2) ?
  
  
• After applying the Border there is code to format the Row height.
  Setting the row height itself is easy enough if you are happy for it to cover the range "B3" plus 1 row through to "LastRow + 3" which mimicks what you seem to be doing now (currently row 4 to 79).
  If you are not starting with a fresh sheet though, then the current delete rows 77:79 are an issue.

​

 

[Alex Blakenburg]

I have made some assumptions if you want to try the below and see if it still does what you need it to do.

Sub FormatReport()
    ' Column deletion section unchanged from Recorded macro
    Columns("A:D").Select
    Range("D1").Activate
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:5").Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select
    Selection.ClearContents
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:R").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:P").EntireColumn.AutoFit
    Columns("Q:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("T:AD").Select
    Selection.Delete Shift:=xlToLeft
    
    ' Add border to Data area
    Dim lr As Long

    Cells.Borders.LineStyle = xlNone
    lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

    Range(Cells(3, "B"), Cells(lr, "S")).Select

    Selection.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium

    ' No change
    Columns("L:L").Select
    Selection.ColumnWidth = 14.14
    Columns("J:J").Select
    Selection.ColumnWidth = 13
    
    ' Removed redundant lines and row delete
    
    ' Changed select to dynamic row version
    Rows("4:" & lr).Select
    
    ' Changed removed duplicated Font size change
    ' removed duplicated Title formatting
    Selection.RowHeight = 12.75
    Range("B1:S1").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With

    Selection.Font.Size = 12
    Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

 

[mikerickson]

I think this will do what you want

Cells.Borders.LineStyle = xlNone
Range("A1").CurrentRegion.BorderAround Weight:=xlThick