Excel Macro Help - Borders

sjohnson77

New Member
Joined
Apr 17, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
822
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 17, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
maybe just using the UsedRange property …​
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
822
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "S").End(xlUp).Row

Then in your code replace this:
VBA Code:
Range("B3:S76").Select

With this:
VBA Code:
    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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

As a total guess maybe

VBA Code:
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

VBA Code:
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
 
Last edited:

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
822
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
822
Office Version
  1. 365
Platform
  1. Windows
I have made some assumptions if you want to try the below and see if it still does what you need it to do.

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,022
I think this will do what you want
VBA Code:
Cells.Borders.LineStyle = xlNone
Range("A1").CurrentRegion.BorderAround Weight:=xlThick
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,412
Latest member
fehr56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top